Dax Query to group by multiple categories from one table

[rayishome]

We have an attribute table that stores dynamic atributes about each product.  For example the table structure is the following

Product, Attribute Name, Attribute Property

A,Color,Red

A,Shape, Box

A,Region, North East

B,Color,Blue

B,Shape, Circle

B, Region, South

C, Color,Blue

C,Share, Box

C,Region, South

Normally this table would be pivoted or would have a column for Color,Shape, & Region, but our model requires the use of dynamic columns / attributes. 

The dax query we need to write needs to to something similar to a group by Color,Share,Region to get a distinct count to produce results like this:

Region, Color, Count

South,Blue,2

North East, Red, 1

or

Color, Shape, Count

Blue, Box, 2

Red, Circle, 1

This is a simple representation of the issue, but in our model is more complex.  We have for example a Sales by Product table and would need to join this dynamic attribute table with the sales table to generate a Sales Total by Color,Shape etc.   

We currently use Pivot within our M Code to produce a dymaic table with columns for Color,Shape, and Region (or what else our users decide to add as attributes).  However, while this solutions works well in Desktop it doesn't work in the Service.  The Service is not capabably of altering the model during a refresh to add the new dynamic column(s) to the model as users add attributes.  Our application can write dynamic DAX so we can fully support any Dax code, but not sure how to approach this and still have decent performance.  

Thanks

[AlexisOlson · Accepted solution]

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.

[rayishome]

Thanks 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.  

[AlexisOlson]

It'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: