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
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?