Forum Discussion

rayishome's avatar
rayishome
Icon for Resolver I rankResolver I
4 years ago
Solved

Dax Query to group by multiple categories from one table

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

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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.

     

5 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      rayishome
      Icon for Resolver I rankResolver I
      4 years ago

      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's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        4 years ago

        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.