Forum Discussion
How to skip dimension slicer selection inside facts table GROUPBY operation
For anyone interested. Finally I've been able to resolve the problem. Started from scratch. According to Ultime Dax Guide, groupby is not really efficient so I did back off from it.
[Segment] and [Year] dimensions are forced by visuals fields and visuals/page filters, so I got rid of these from syntax.
Calculate runs sums of [Hours] iterating through all distinct products (if i get the logic correctly). ALLEXCEPT delivers to DISTINCT full list of relevant products ignoring [Country] slicer refinement.
In the end neither SUMMARIZE nor GROUPBY are needed in my scenario.
MAXX(DISTINCT(ALLEXCEPT(Products,Products[ProductSegment])),CALCULATE(SUMX('ProductCosts','ProductCosts'[Hours])))
Community SupportHi PawelJanczak ,
How about this?
Max per proces =
VAR _prodsums =
Groupby(
ALL('ProductCosts'),
'Calendar'[Year],
'Products'[ProductSegment],
'Products'[Country],
'ProductComponents'[ComponentName],
"Sum",SUMX(CURRENTGROUP(),'ProductCosts'[Hours])
)
Return
MAXX( _prodsums, [Sum] )
If this doesn't work, please share me your expect result with an example.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the reply Icey. Already tried that. Maybe I missed some requirements, sorry.
Not all facts should be considered in the calculation, they are narrowed by page filter. Only sample matching specific dimensions is important. Then I'm showing some visuals which are narrowed to single product component, thus trying to write a measure which will consider the visal level filters as well.
Running ALL over the table leads to:
- skipping the [Country] refinement, which is good, but
- skipping the [Year] refinement
- skipping the [ProductSegment] refinement
- skipping the [ProductComponent] refinement
- calculating and considering sums for irrelevant dimensions; measure returns top sum which in my scenario is sum of all facts not matching any keys, have some garbage data from 2017 loaded to model, while calendar starts from 2018:
This single number is shown entire time regardless of page/visual/slicer filters, so the grouping happens, but MAXX has too many irrelevant elements to process through.
EDIT: to be more precise, table variable should be narrowed to selected Country, Selected product Segment, Selected Product Component while sums out of all available countries should be validated by maxx.