none
PQ Slicers RRS feed

Answers

  • Herbert

    Re-downloaded your version where you grouped previous tables 3,5,6 as Table3

    • No change to your Table2
    • Delete your 3 lists: ModelL, ColorL and FuelL
    • Table3 as Connection only with code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
        ChangedTypes = Table.TransformColumnTypes(Source,{{"Model", type text}, {"Color", type text}, {"Fuel", type text}}),
        ReplacedEmptys = Table.ReplaceValue(ChangedTypes,"",null,Replacer.ReplaceValue,{"Model", "Color", "Fuel"}),
        ReplacedAlls = Table.ReplaceValue(ReplacedEmptys,"All",null,Replacer.ReplaceValue,{"Model", "Color", "Fuel"})
    in
        ReplacedAlls

    Table4 code:

    let
       Source = Table2,
       ModelL = List.Buffer(List.RemoveNulls(Table3[Model])),
       ColorL = List.Buffer(List.RemoveNulls(Table3[Color])),
       FuelL = List.Buffer(List.RemoveNulls(Table3[Fuel])),
    
       FilteredModel =
          if List.IsEmpty(ModelL) then Source
          else Table.SelectRows(Source, each List.Contains(ModelL,[Model])),
       FilteredColor =
          if List.IsEmpty(ColorL) then FilteredModel
          else Table.SelectRows(FilteredModel, each List.Contains(ColorL,[Color])),
       FilteredFuel =
          if List.IsEmpty(FuelL) then FilteredColor
          else Table.SelectRows(FilteredColor, each List.Contains(FuelL,[Fuel])),
          
       QueryResult =
          if List.IsEmpty(List.Combine({ModelL,ColorL,FuelL}))
          then Source
          else FilteredFuel
    in
       QueryResult

    Works better?

    Thursday, February 13, 2020 3:45 PM

All replies

  • Hi Herbert

    Not sure what you exactly meant with more robust - a bit of precision next time please :)

    I'm not familiar with CUBEs so not sure this is doable… If you can, at the Excel level, provide the values shown in in E2, H2 and K2 (or All instead of 0) there's a possible optimization in the formulas that would also avoid a 2nd ReplaceValue in TableFromDM query that replaces your Table3, Table5 and Table6 in this proposal

    Wednesday, February 12, 2020 2:37 PM
  • Thanks Lz._.
    I combined Tables3,5,6 into one Table3. Better.
    Updated link.
    Still have to "Refresh All" and then refresh Table4 separately. Slow.
    Your link says "Read Only".

    Wednesday, February 12, 2020 4:25 PM
  • Hi Herbert

    In the meantime I found my way with the CUBE aspect and revised the formulas accordingly. Same link as previous + Yes you can't modify it Online

    Re. Still have to "Refresh All" and then refresh Table4 separately. Slow
    not sure I understand? You only have to Refresh Table4 once you've selected items on the Slicers, no?

    Wednesday, February 12, 2020 4:44 PM
  • Herbert

    Forgot 2 formulas in sheet Aux :((. Fixed, same link

    FYI: from what I've observed so far - on 1st load/refresh after opening XL - what takes most of the time to evaluate/execute is the "connection" to the Data Model, not the Table4 query and its dependencies

    Wednesday, February 12, 2020 5:19 PM
  • I combined Tables3,5,6 into one Table3. Better.
    Updated link.
    Still have to "Refresh All" and then refresh Table4 separately. Slow.

    Hi Herbert

    (your link point to your initial version). Looked at things again and made a tiny optimization in Excel to prevent recalc. a couple of things in Table3 that summarizes items selected in the 3 Slicers - updated version here

    However, this is isn't going to make any significant improvement from a speed perspective. From what I've experimented/observed a few times the bottleneck is on 1st refresh after opening Excel, where time to Load the Data Model + the PQ modules is "incompressible" AFAIK
    FYI, on a pretty standard laptop (Core I3 + HDD - no SSD) Table4 refreshes in less than a second, after a 1st refresh

    Not quite sure I can do anything else re. this :( Hope this helps anyway 

    Thursday, February 13, 2020 7:39 AM
  • Lz._,
    It takes 24 seconds after the 1st refresh on my desktop.
    Could not make any of your optimizations since your links don't work for me.
    Neither do my links work for you.
    Stalemate.
    Thanks for all your effort.

    Thursday, February 13, 2020 3:04 PM
  • Herbert

    My link points to OneDrive and 99% of OPs I share samples with can access them no problem. Restrictions on your side?

    Give me a moment and I'll doc what I initially suggested without the tiny optimizations on the Excel side
    In the meantime, how many rows are involved in your source (Table2) so this takes > 20 seconds?

    Thursday, February 13, 2020 3:19 PM
  • Herbert

    Re-downloaded your version where you grouped previous tables 3,5,6 as Table3

    • No change to your Table2
    • Delete your 3 lists: ModelL, ColorL and FuelL
    • Table3 as Connection only with code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
        ChangedTypes = Table.TransformColumnTypes(Source,{{"Model", type text}, {"Color", type text}, {"Fuel", type text}}),
        ReplacedEmptys = Table.ReplaceValue(ChangedTypes,"",null,Replacer.ReplaceValue,{"Model", "Color", "Fuel"}),
        ReplacedAlls = Table.ReplaceValue(ReplacedEmptys,"All",null,Replacer.ReplaceValue,{"Model", "Color", "Fuel"})
    in
        ReplacedAlls

    Table4 code:

    let
       Source = Table2,
       ModelL = List.Buffer(List.RemoveNulls(Table3[Model])),
       ColorL = List.Buffer(List.RemoveNulls(Table3[Color])),
       FuelL = List.Buffer(List.RemoveNulls(Table3[Fuel])),
    
       FilteredModel =
          if List.IsEmpty(ModelL) then Source
          else Table.SelectRows(Source, each List.Contains(ModelL,[Model])),
       FilteredColor =
          if List.IsEmpty(ColorL) then FilteredModel
          else Table.SelectRows(FilteredModel, each List.Contains(ColorL,[Color])),
       FilteredFuel =
          if List.IsEmpty(FuelL) then FilteredColor
          else Table.SelectRows(FilteredColor, each List.Contains(FuelL,[Fuel])),
          
       QueryResult =
          if List.IsEmpty(List.Combine({ModelL,ColorL,FuelL}))
          then Source
          else FilteredFuel
    in
       QueryResult

    Works better?

    Thursday, February 13, 2020 3:45 PM
  • Nice.
    Lightning fast.
    Updated link.

    Friday, February 14, 2020 12:39 AM
  • Updated link.

    Still getting an old version :( anyway… There are a few possible optimizations on the Excel side but w/o a file sharing option, not easy

    However, one thing you should do - if not already done - is to comment or remove the formulas in E1:I1 on sheet Aux. All use RANDomizing functions (I guess you used them to create dummy data) and recalc. on almost every action within Excel, including selecting item(s) in the Slicers

    Hope this makes sense & helps. Stay tuned, I'll see if I can share my file on a different service w/o creating a new account somewhere

    EDIT: Try that link & let me know please
    • Edited by Lz._ Friday, February 14, 2020 11:20 AM
    Friday, February 14, 2020 10:43 AM
  • Your latest link works and is what I have seen everybody else use.
    I can download and edit the file.
    But your Excel contains the Sequence() function that I do not have.
    I am using Office 365 Pro Plus, version 1908, Build 11929.20606.
    Your file also uses R1C1 (A1) which is anathema to me.
    Here is a link to another file sharing service that has a 99.7% success rate:
    https://5cbc757adfe362e17e59-196baa6296f9274c6752718f77fbfab4.ssl.cf1.rackcdn.com/01_15_20d.xlsx

    I am completely satisfied with my current version that you helped co-author.

    Friday, February 14, 2020 3:29 PM
  • But your Excel contains the Sequence() function that I do not have.
    I am using Office 365 Pro Plus, version 1908, Build 11929.20606
    OK, forget. I assumed you were fully up to date but you might be on a semi-annual channel or something like that
    Your file also uses R1C1 (A1) which is anathema to me.
    This doesn't come from "me". Each time I downloaded and opened one of Your files, my Excel switched to R1C1
    Here is a link to another file sharing service that has a 99.7% success rate
    https://5cbc757adfe362e17e59-196baa6296f9274c6752718f77fbfab4.ssl.cf1.rackcdn.com/01_15_20d.xlsx

    Still getting an old version

    I am completely satisfied with my current version that you helped co-author

    OK, let's close that case then


    Friday, February 14, 2020 3:43 PM