Forum Discussion
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...
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] ) ) ) )
Best Regards,
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?