Counting responses within multiples fields and combining into a column chart

[ejoneslor]

I have a dataset (example below and ingested as a .csv file) that I need to represent as a column chart.

Along the X axis I want to show each of the performed checks (headers of columns 3-8) and on the Y axis I want a count of how many times the column includes a 'Fail' response. So for the given dataset I would expect to see 5 columns of height 2 and 1 column (Grease Gun) of height 1.

I'm assuming I need some form of measure including a loop but I can't conceive of what?

Ideally I'd like the measure to be flexible so if additional columns were added to the dataset, they would get picked up.

can anyone assist please?

I cannot add in any calculations in the source file.

Ed

Form Ref	Asset Name	Check Oil Lvl	Water Lvl	Thorough Exam/Test Cert In Date	Inspection/Defect Book	Grease Gun	Rope Spray
CCPDI-00001		Fail	Not Checked	Fail	Not Checked	Not Checked	Not Checked
CCPDI-00002		Fail	Not Checked	Pass	Fail	Fail	Pass
CCPDI-00003		Not Checked	Fail	Not Checked	Not Checked	Not Checked	Not Checked
CCPDI-00000		Not Checked	Not Checked	Not Checked	Fail	Not Checked	Fail
CCPDI-00023	CC Test Diesel Tele	Not Checked	Fail	Fail	Not Checked	Not Checked	Fail

[parry2k · Accepted solution]

@ejoneslor you need to unpivot your data. Transform data -> select first two columns -> right click -> Unpivot other columns.

It will give you two columns, attribute and value, rename these columns if you want. Close and apply.

Add following measure:

Fail Count = CALCULATE ( COUNTROWS ( Table ), Table[Value] = "Fail" )

To visualize, use attribute on x-axis and above measure (Fail Count) on values and that will do it.

[parry2k]

@ejoneslor I don't want to say @katika555  SOLUTION is bad but it is not the right approach and there is un-necessary work required. It is completely against the best practice and not at all a scalable solution and over time when data grows it will not perform very well. 

Having said that, the solution I provided is the way to go, and I am not sure why it worked for you. See the attached file which has the same solution (I provided) and it works as expected.

There are always many ways to solve a problem, if you follow the best practices, it will go a long way otherwise you will be just fixing the problems. Good luck!

[ejoneslor]

I have revisited your solution and checked why I was seeing a differnet result. It seem my dataset was slightly different to that posted above and this was causing the issue.
I've amended the source and now mine works as yours does. Excellent. Thank you.

Ed

[katika555 · Accepted solution]

Hi,
you can produce with switch and static table:

1. create 5 measures for your columns : 

Cnt COL = CALCULATE(COUNTROWS(DD),FILTER(DD, DD[Check Oil Lvl]="Fail"))
Cnt GG = CALCULATE(COUNTROWS(DD),FILTER(DD, DD[Grease Gun]="Fail"))
Cnt Ins = CALCULATE(COUNTROWS(DD),FILTER(DD, DD[Inspection/Defect Book]="Fail"))
....

2. Create Table with Colun Names and Ordering what you want

3. Create Measure with Switch:

Mes Matrix = 
 SWITCH(
     SELECTEDVALUE(ForSwitch[ColName]),
     "Check Oil Lvl",[Cnt COL],
     "Water Lvl",[Cnt WL],
     "Thorough Exam/Test Cert In Date", [Cnt TE],
     "Inspection/Defect Book", [Cnt Ins],
     "Grease Gun",[Cnt GG],
     "Rope Spray",[Cnt RS]
    )

4. add on chart this way:

5. 

Enjoy Power BI

[ejoneslor]

Fantastic, thank you.
It looks like any addiitonal columns will need to be manually added buyt this should be a reaosnably rare occurence.
Thank you for your help.

[parry2k · Accepted solution]

@ejoneslor you need to unpivot your data. Transform data -> select first two columns -> right click -> Unpivot other columns.

It will give you two columns, attribute and value, rename these columns if you want. Close and apply.

Add following measure:

Fail Count = CALCULATE ( COUNTROWS ( Table ), Table[Value] = "Fail" )

To visualize, use attribute on x-axis and above measure (Fail Count) on values and that will do it.

[ejoneslor]

@parry2k Thanks very much for this.
I've followed your steps and I'm afraid it doesn't quite give me what i'm looking for. That solution provided me with a single column with the count of all 'Fail' entries, 11 in this case.
What I'm looking for is a x-axis with 6 columns, Check Oil Lvl, Water Lvl, Thourough Exam, Inspection, Grease Gun, Rope Spray with the 'Fail' count related to each.

Ed