Filtering multiple columns from the same table

[bm_data]

I have a Power app that writes data to a SharePoint list. It is a series of questions that all have a choice answer of "safe" or "unsafe". What I would like to do is pull all the questions where "unsafe" is the answer and then drill down by which specific question was answered "unsafe". There are probably 50 total questions (columns) that could have a value of "unsafe" so I was hoping to use a filter but unable to get that working. 

[Anonymous]

What if the data was tabled differently? Would this process work for the data below?

Attribute	Safe	Unsafe
Test1	Marketing	Marketing
Test2	Sale	Marketing
Test3	Facilities	Sale
Test4	Marketing	Sale
Test5	Sales	Facilities

[v-zhangti]

Hi, @bm_data 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with Excel. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bm_data]

This is an rough example (missing date, creator, ect.) of what the data looks like. The expected outcome would be a bar chart showing each Test (column) that was unsafe tallied up. So the bar chart would be test1 =2, Test2 =2, Test3 =0, Test4 =3 and Test5 =4. Additionally I would like to then click into that bar and see the details of the unsafe values such as business unit, date and so on.

[v-zhangti · Accepted solution]

Hi, @bm_data 

You need to transform your data in Power Query. First, copy into 5 tables, each table with the first column and a Test.

For example, tables 1 and 2.

Select the test column and click Unpivot Column to turn each table into something like this.

Then use Append to merge these 5 tables together.

Result:

Go back to Desktop and use Dax to calculate the number of unsafes.

Measure = 
CALCULATE (
    COUNT ( Append1[Attribute] ),
    FILTER (
        ALL ( Append1 ),
        [Value] = "Unsafe"
            && [Attribute] = SELECTEDVALUE ( Append1[Attribute] )
    )
) + 0

If you want to click to view the details, you can put the fields you want to see in the tooltip.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.