Forum Discussion
How to skip dimension slicer selection inside facts table GROUPBY operation
Hi All,
I'm tryign to resolve a problem for training purposes mainly (I have a workaround but it's basicaly several gruping operations in power query and output "stats table" loaded to model - not really a fast solution).
In my model I have a:
Fact table ProductCosts with columns:
[DateKey] <- *:1 relationship with 'Calendar' table
[ProductKEY] <- *:1 relationship with 'Products' table
[Hours] - work time contributed toward a product
[ComponentKEY] <- *:1 relationship with 'ProductComponents' table
Products table:
[ProductKEY]
[Country]
[ProductSegment]
ProductComponents table:
[ComponentKEY]
[ComponentName]
Calendar table as date table:
[DateKEY]
[Year]
[Month]
[WeekNum]
There is no relationship between Products and Components tables.
Now I'm trying to sum time that was invested in a year, in a product segment under specific component per each country separately, and then return MAXX out of all underlying sums. I'm able to summmarize the data so i get sums grouped by year + segment + country + component (dax studio confirmed). The tricky part is I'm filtering visual with slicers:
'Products'[Country]
'Products'[Segment]
'Calendar'[Year]
Since I'm trying to show particular country result + max out of similar group (as a reference point), I'm stucked. Slicers lead to maxx being calculated for single data point (narrowing by [Segment] and [Year] is expected), i'm trying to skip the 'Products'[Country] selection with no success. The syntax is:
Max per proces =
VAR _prodsums =
Groupby(
'ProductCosts',
'Calendar'[Year],
'Products'[ProductSegment],
'Products'[Country],
'ProductComponents'[ComponentName],
"Sum",SUMX(CURRENTGROUP(),'ProductCosts'[Hours])
)
Return
MAXX( _prodsums, [Sum] )
Any hints would be appreciated! 🙂
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])))
4 Replies
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])))- Icey
Community Support
Hi PawelJanczak ,
Glad to hear that. Please accept your reply as a solution so that people who may have the same question can get the solution directly.
Best Regards,
Icey
- Icey
Hi 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.
