Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Summarize multiple columns based on values

I am stuck with an issue I cant seem to figure out how to do in DAX.  Since users will select filters in PowerBI Services, I can't do this in Power Query/M as some materials would be filtered out and...
Need Help
moumipanja's avatar
moumipanja
Microsoft Employee
7 years ago

To unpivot a table using DAX is quite challenging. You may want to try the following steps in DAX to get your desired result.

 

1. Creating a new table with two columns, 'Rules' and 'Status'

new table = UNION(
SELECTCOLUMNS(Sheet2, "Status", Sheet2[Rule 1], "Rules", "Rule 1"),
SELECTCOLUMNS(Sheet2, "Status", Sheet2[Rule 2], "Rules", "Rule 2"),
SELECTCOLUMNS(Sheet2, "Status", Sheet2[Rule 3], "Rules", "Rule 3"))
 
You will be able to get a table like this:
 
2. Then you can use a matrix format to display your result
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Sorry for confussion, but I need to create a summary table in DAX, because i am pushing to R which is limited to 150,000 rows and my data set is 3 million records and increasing.  So, i cant use a widget (like a matrix) to summarize the data.

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

     

    You can get it with the formula below. But this calculated table is relative stable, which means its values can't change as the filters or slicers change.

    Table =
ADDCOLUMNS (
    DISTINCT (
        UNION (
            VALUES ( Table1[Rule-1] ),
            VALUES ( Table1[Rule-2] ),
            VALUES ( Table1[Rule-3] )
        )
    ),
    "Rule-1-num", CALCULATE (
        COUNT ( Table1[Rule-1] ),
        FILTER ( 'Table1', 'Table1'[Rule-1] = EARLIER ( 'Table1'[Rule-1] ) )
    ),
    "Rule-2-num", CALCULATE (
        COUNT ( Table1[Rule-2] ),
        FILTER ( 'Table1', 'Table1'[Rule-2] = EARLIER ( [Rule-1] ) )
    ),
    "Rule-3-num", CALCULATE (
        COUNT ( Table1[Rule-3] ),
        FILTER ( 'Table1', 'Table1'[Rule-3] = EARLIER ( Table1[Rule-1] ) )
    )
)

    Summarize-multiple-columns-based-on-values

     

     

    Best Regards,

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I was able to get your solution to work, but as you mentioned when a user filters with a slicer the summary table does not update.  This update is a key feature for my solution.  Is there no way to create a table in dax that is updateable based on user selections?