help with calculation of cells containing certain text

[fstupot]

hi guys,

really hope you can help xxx

i am trying to show a report that shows a list of scores for when certain inspections are carried out.

if a question is applicable and it passes, the outcome is 1. if there is a fail it shows as a 0. it the asks to rate hos significant the defect is in the next column (ofi, minor or major)

my issue is that i want to show a table that shows what question is a failed, with the type of defect rating it has. because of the the stupid third party software it kiscks the data out in lateral rows as per below and the sinpections have over 80 questions , so i can really use something where i have to type all the columns out in dax.

ideally i would like to see what question it has failed on and the type of defect rating that has been typed in the column after. i tried unpivoting columns but it only lets me view what question fail, or the type of defect on a table....not both at the same time.

please help and i will love you forever!!x

[fstupot]

to explain better here is a table showing example data and my desired output x 

inspection carried about by

question 1

defect rating 1

question 2

defect rating 2

question 3

defect rating 3

inspection carried out by

question fail

defect rating

dave

1

1

1

steve

question 1

major

steve

1

1

1

will

question 3

minor

steve

0

major

1

0

minor

steve

question 3

minor

ben

1

1

1

will

1

1

0

minor

[v-stephen-msft]

Hi @fstupot ,

Please try unpivotting columns. 

Select the first column and click "Unpivot Other Columns".

Filter out 1, 0 and blank and below is the expected result.

Best Regards,

Stephen Tao

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