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 IWhat 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!
Super UserIf 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: