Summarize multiple columns based on values

Question

I am stuck with an issue I cant seem to figure out how to do in DAX.&nbsp; 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 would require a change to the summary result table.&nbsp; Any assistance would be appreciated.&nbsp;I have a set of data like below (but with many more rules &amp; materials)&nbsp;Material&nbsp; &nbsp;Rule-1&nbsp; Rule-2 Rule-3A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Pass&nbsp; Fail&nbsp; &nbsp; &nbsp; &nbsp;N/AB&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Fail&nbsp; &nbsp;Pass&nbsp; &nbsp; PassC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; N/A&nbsp; Fail&nbsp; &nbsp; &nbsp;Pass&nbsp;And I want to create a table like&nbsp;&nbsp;Status&nbsp; Rule-1 Rule-2 Rule-3Pass&nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2Fail&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0N/A&nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp;or it could be pivoted (rules down left and Pass, Fail, N/A across top) does not matter.&nbsp;How would I accomplish this?

moumipanja · Answer

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

v-jiascu-msft · Answer

Hi&nbsp;Anonymous,
&nbsp;
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&nbsp;=
ADDCOLUMNS&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;DISTINCT&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UNION&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VALUES&nbsp;(&nbsp;Table1[Rule-1]&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VALUES&nbsp;(&nbsp;Table1[Rule-2]&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VALUES&nbsp;(&nbsp;Table1[Rule-3]&nbsp;)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)
&nbsp;&nbsp;&nbsp;&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;"Rule-1-num",&nbsp;CALCULATE&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COUNT&nbsp;(&nbsp;Table1[Rule-1]&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;'Table1',&nbsp;'Table1'[Rule-1]&nbsp;=&nbsp;EARLIER&nbsp;(&nbsp;'Table1'[Rule-1]&nbsp;)&nbsp;)
&nbsp;&nbsp;&nbsp;&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;"Rule-2-num",&nbsp;CALCULATE&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COUNT&nbsp;(&nbsp;Table1[Rule-2]&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;'Table1',&nbsp;'Table1'[Rule-2]&nbsp;=&nbsp;EARLIER&nbsp;(&nbsp;[Rule-1]&nbsp;)&nbsp;)
&nbsp;&nbsp;&nbsp;&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;"Rule-3-num",&nbsp;CALCULATE&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COUNT&nbsp;(&nbsp;Table1[Rule-3]&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;'Table1',&nbsp;'Table1'[Rule-3]&nbsp;=&nbsp;EARLIER&nbsp;(&nbsp;Table1[Rule-1]&nbsp;)&nbsp;)
&nbsp;&nbsp;&nbsp;&nbsp;)
)

&nbsp;
&nbsp;
Best Regards,

anonymous · Answer

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.&nbsp; So, i cant use a widget (like a matrix) to summarize the data.

anonymous · Answer

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.&nbsp; This update is a key feature for my solution.&nbsp; Is there no way to create a table in dax that is updateable based on user selections?

Forum Discussion

Summarize multiple columns based on values

4 Replies

Related content

Summarize Multiple Columns

Parameter in summarize

Summarize multiple tables in DAX

Don't Summarize....still summarizes...?

Sequential Order column based on multiple criteria