Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am trying to visualize the performance of a cleaning process that occurs at 85 different objects. The main goal is showing the performance of the process in a particular year, month, week and on case level which I am able to show with slicers. However, I would like to add a feature that shows a benchmark of the 10 'fastest' cases for each object.
Only cases that are 'complete' should be taken in consideration for this benchmark. A process instance is completed whenever the particular process contains a [Start_process] and [End_process] record and the time to complete takes 90 minutes or more.
To do this I have a header and detail table available. The tables are connected to each other by an unique BATCH_ID. The picture below shows an example of the header- and detail table. In the example below the only case that should be taken in consideration for the benchmark is the case with BATCH_ID 3, because a) it contains a [Start_process] and [End_process] record and b) its duration is longer than 90 minutes. Allthough the incomplete cases should not be used for the benchmark, they should be shown in a visualization that shows all cases of the selected month.
In an ideal scenario the solution would look something like the picture below with a changing benchmark whenever the selected object changes.
As I am new to Power BI I am not quite sure whether the solution lies in a measure, smart use of DAX, additional slicers or something completely different.
In short, the main challenges are the following bullet points (and combining them in a proper solution!):
Thanks in advance,
Davo
HI, @Anonymous
You may try to add a calculate column in header table by this formula
Column = IF(CALCULATE(COUNTA(detail[Object]),FILTER(RELATEDTABLE(detail),detail[Object]=Header[Object]&&detail[Process_step]="End_process"))>=1&&Header[Total_Time]>=90,Header[Total_Time])
then use this column as a benchmark.
Best Regards,
Lin
Hi, @v-lili6-msft
Thanks for your reply and the advice! I am having some trouble while implementing your solution. Whenever I try to create the calculated column as you suggested I get the following error:
"DAX comparisons operations do not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."
I do not entirely understand this warning. The outcome of the calculated column should be an average for each object, if I understand your solution correctly. I dont see where there would be a comparison between a boolean and text.
Could u help me debug this error?
Kind Regards,
Davo
User | Count |
---|---|
127 | |
108 | |
100 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |