Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 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!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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
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
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.
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |