Forum Discussion
Resolver IDax Query to group by multiple categories from one table
You can calculate a pivoted version and then do a SUMMARIZE or GROUP BY.
Summary = VAR PivotCols = SUMMARIZECOLUMNS ( T[Product], "Region", CALCULATE ( SELECTEDVALUE ( T[Property] ), T[Name] = "Region" ), "Color", CALCULATE ( SELECTEDVALUE ( T[Property] ), T[Name] = "Color" ) ) RETURN GROUPBY ( PivotCols, [Region], [Color], "Count", SUMX ( CURRENTGROUP (), 1 ) )I don't know how well this will perform for big/complex models but at least it does in principle.
Resolver IThanks this is a great solution that solves the problem for the sample data. But when Region has more than one color the SelectedValue returns blank.
Super UserIt's hard to solve problems that haven't been specified. 😉
If you have data like:
B,Color,Blue
B, Region, South
B, Region, North
B,Color,Red
It's not possible to know which colors are associated with which regions without more information. It could be that you can have multiple colors per region. On the other hand, maybe there's always only one. You might handle these cases differently.
- rayishome
What I need is a function that works like the Pivot function in SQL/M Code that will create column(s) out of the Attribute Name and place the distinct values from the Attribute Property into the new column assoiciated with the Product. Or a way to Summarize a Sub Table that's filtered. Your solution would work if we could use Values vs SelectedValue. Fully understand why Values won't work here becuase we are using an expression vs a Field to Summarize by.
I was able to solve this somewhat by create two Vars. One Summarized by Product filtered for "Region" and adding a blank column for the Color. Create a second Var but filtered for "Color" and creating a blank Region column then unioned the data and grouped it using the max value from each Region and Color column. Very messy, but it creates a pivoted table by Product. The only issues is I can't figure out out use this new table to join with actual tables.
Thanks again!
- AlexisOlson
If you have multiple colors for a single product, then pivoting in M won't work either without additional assumptions or steps. Can you give a simplified example of input and result for these cases that weren't covered by your initial example?
Input:
Pivot attempt: