Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
Here is the screnario:
I want to apply color Palette to below First Level items based on the sublevel calculation.
First Level:
Under BCO - Second Level / Sub Level items:
Query : Want to Apply color on First Level, based on Second level Least % item.
For Example: First Level item BCO, it is holding the 10 items at Sub Level (after drill thru) & Minimum among the 10 item is 77.84%.
Requirement : Now on the First Level BCO should be also RED as one of the sub level item is RED.
Logic : If value is less than 80% then RED.. and few other condition for other colors.
Please help me out here with DAX funciton where we can apply colors.
Thank you in Advance.
Regards,
Guru
@lbendlin Posted sample data.
I need a DAX to apply below logic:
For BCO, it should be red -> as at the sub level one of the entity is red(Screenshot FYR)
@lbendlin Thanks For your time and help!
But my scenario is little different here , Avg Unit Value is measure containing Cumulative of last 6 month - which we cannot pass as Parameter to MIN().
So, I have attached PBI File link below, Please let me know if you can help me with cumulative avg.
Thank you in advance.
Regards,
Guru
This formula
Avg Unit Value =
var selected_date = DATE(YEAR(SELECTEDVALUE('DIM CAL'[Date])),MONTH(SELECTEDVALUE('DIM CAL'[Date])),1)
var Last6 = DATE(YEAR(selected_date),MONTH(selected_date)-5,1)
VAR Cal = CALCULATE( [AVERAGE UNIT VALUE],FILTER(ALL('DIM CAL'),'DIM CAL'[Date]<=selected_date && 'DIM CAL'[Date]>Last6))
return Cal
is incorrect. You should use DATEADD or EDATE to identify the last six months.
Anyway, I don't understand why you cannot pass the cumulative to the minx calculation. Please explain.
@lbendlin YES Dateadd () can be used but i'm getting correct values using current DAX.
and Cumulative I'm passing it to Minx but it not providing desired results:
Plant | Tech ID | Avg Unit Value |
BCO | BCO-30-1000 UCWT | 92.86% |
BCO | BCO-BFW (EOE) | 95.83% |
BCO | BCO-BFW (UTE) | 100.00% |
BCO | BCO-BOILER | 100.00% |
BCO | BCO-CHILLED WATER | 76.56% |
BCO | BCO-CONDENSATE | 90.63% |
BCO | BCO-HOT WATER | 100.00% |
BCO | BCO-RX BOILER A | 100.00% |
BCO | BCO-RX BOILER B | 100.00% |
BGO | BEO U 1200 CT | 89.04% |
BGO | BEO U 121 CT | 90.83% |
BGO | BEO U 122 CT | 90.38% |
BGO | BEO U 124 CT | 93.44% |
BGO | BEO U PE CT | 91.75% |
BGO | BEO U PLP CT | 79.34% |
BGO | BEO U PP CT | 85.84% |
BGO | BEO-UCA-B-F1101 | 98.15% |
BGO | BEO-UCA-B-F1101-SAT | 100.00% |
BGO | BEO-UCA-B-F1101-SUP | 100.00% |
BGO | BEO-UCA-B-F1102 | 100.00% |
BGO | BEO-UCA-B-F1102-SAT | 100.00% |
BGO | BEO-UCA-B-F1102-SUP | 100.00% |
BGO | BEO-UCA-BFW | 100.00% |
BGO | BEO-UCA-COND-POLSD | 100.00% |
BGO | BEO-UCA-COND-RET-BAD | 91.67% |
BGO | BEO-UCA-COND-RET-OKE | 94.44% |
BGO | BEO-UCA-DEMIN1 | 100.00% |
BGO | BEO-UCA-DEMIN2 | 100.00% |
BGO | BEO-UCA-DEMIN-OUT | 100.00% |
BGO | BEO-UCB-BFW-V140-30 | 98.53% |
BGO | BEO-UCB-BFW-V140-31 | 98.61% |
BGO | BEO-UCB-BOILER-F143 | 100.00% |
BGO | BEO-UCB-COND-U111 | 100.00% |
BGO | BEO-UCB-DEMIN-U131 | 94.44% |
BLO | BLO-R - 1001C | 100.00% |
BLO | BLO-U1801 BP1 CWT | 96.30% |
BLO | BLO-U1802 BP2 CWT | 100.00% |
BLO | BLO-U1803 BP3 CWT | 98.15% |
BYO | BYO C CHILLER | 90.48% |
BYO | BYO CATALLOY CHILLER | 100.00% |
BYO | BYO CATALLOY JACKET | 92.31% |
BYO | BYO C-LINE JACKET | 92.86% |
BYO | BYO D CHILLER | 94.44% |
BYO | BYO D-LINE JACKET | 88.89% |
BYO | BYO E CHILLER NH3 | 100.00% |
BYO | BYO E CHILLER PROPYL | 95.24% |
BYO | BYO E-LINE JACKET | 95.24% |
BYO | BYO-OLD CLX CWT | 95.65% |
BYO | BYO-OLD DLX CWT | 100.00% |
BYO | BYO-OLD ELX CWT | 100.00% |
BYO | BYO-OLD MARLEY CWT | 100.00% |
BYO | BYO-OLD MP3 CWT | 100.00% |
BYO | BYO-OLD MPX CWT | 100.00% |
BYO | BYO-OLD XL CWT | 100.00% |
Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services.
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |