Filtering Help

[Hewittbt]

I have a csv file that is the only way I can get Inspection results out of a piece of software.  There are actually 75 tasks, each one with a "Pass, Fixed, Failed" result.  The output is absolutely not readable, There are a bunch of other fields that I can ignore.  A simplified example table of my data is below.  All I really care about are the locations that have tasks that either Failed or Fixed.  I would like to try to get an output such as the desired output below, I just don't know how to even get started on this i guess.  I don't want any of the Passed tasks to show up in the list output.  I don't even know how to concisely ask what I am looking for to find a video tutorial or answer.  Any help is greatly appreciated!

Table a:

Location	Task 1 Name	Task 1 Value	Task 2 Name	Task 2 Value	Task 3 Name	Task 3 Value	Task 4 Name	Task 4 Value
Location A	Window	Passed	Door	Passed	Walls	Passed	Floor	Passed
Location B	Window	Fixed	Door	Passed	Walls	Passed	Floor	Failed
Location C	Window	Passed	Door	Failed	Walls	Failed	Floor	Fixed

Desired Output:

Location A

Location B

Window - Fixed

Floor - Failed

Location C

Door - Failed

Wall - Failed

Floor - Fixed

[ExcelMonke · Accepted solution]

Hello,
The first thing I would do is to transform your data in the query editor. I would unpivot your Task Name columns into a single column, same as your task value. Your result should come down to 3 columns (based on your simplified datatable): Location; Task Name; Task Value. 

From there it becomes a little simpler getting your desired result. You can simply place it in a matrix table and filter out "Passed". This should get you your desired result

[v-jianpeng-msft · Accepted solution]

Your solutions is great @ExcelMonke . Allow me to provide more detailed steps:

Hi, @Hewittbt 

First of all, you can reverse pivot the columns of Task 1, Task 2, Task 3, and Task 4:

Next, you need to create a measure:

status = 
VAR _currenttask = SELECTEDVALUE('Table'[Value])
RETURN 
SWITCH(TRUE(),
    _currenttask="Window",CALCULATE(MAX('Table'[Task 1 Value]),FILTER('Table','Table'[Value]=_currenttask)),
    _currenttask="Door",CALCULATE(MAX('Table'[Task 2 Value]),FILTER('Table','Table'[Value]=_currenttask)),
    _currenttask="Walls",CALCULATE(MAX('Table'[Task 3 Value]),FILTER('Table','Table'[Value]=_currenttask)),
    _currenttask="Floor",CALCULATE(MAX('Table'[Task 4 Value]),FILTER('Table','Table'[Value]=_currenttask))
)

Then, as shown in the following image, place the corresponding fields in the matrix visual:

The results are as follows:

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[ExcelMonke · Accepted solution]

Hello,
The first thing I would do is to transform your data in the query editor. I would unpivot your Task Name columns into a single column, same as your task value. Your result should come down to 3 columns (based on your simplified datatable): Location; Task Name; Task Value. 

From there it becomes a little simpler getting your desired result. You can simply place it in a matrix table and filter out "Passed". This should get you your desired result

[Hewittbt]

I think that is exactly what I want to do.  Thankyou!  I did not know "unpivoting columns" was a thing!

[ExcelMonke]

You're welcome! Yes, this is a handy little trick I learned a couple years ago and saved me many headaches 🤠. If my answer resolved your issue, please mark it as a solution so that others may find it helpful too!