Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello All,
I have below example data set
ID | Project | Summary | Progress% | Start Date |
1 | A | AAA | 100% | 11/23/2022 |
2 | A | AAA | 11/23/2022 | |
3 | A | BBB | 80% | 11/23/2022 |
4 | B | CC | 90% | 11/23/2022 |
5 | B | DD | 95% | 11/23/2022 |
6 | B | DD | 100% | 11/23/2022 |
7 | C | EE | 25% | 1/1/2023 |
8 | C | FF | 1/1/2023 | |
9 | C | GG | 20% | 1/1/2023 |
In the above example dataset Progress%, Start Date are calculated columns which I obtained from other table using Lookup value.
I am trying to create a measure to show above data in a Matrix with below conditions
IF Project A is in First two weeks from Start Date & Progress BETWEEN 20-25% show Progress% in Yellow color
IF Project A is in First two weeks from Start Date & Progress <20% show Progress% in RED color
IF Project A is in First two weeks from Start Date & Progress=25% show Progress% in Green color
IF Project A is in > two weeks & <= four weeks from Start Date & Progress BETWEEN 45-50% show Progress% in Yellow color
IF Project A is in > two weeks & <= four weeks from Start Date & Progress<45% show Progress% in RED color
IF Project A is in > two weeks & <= four weeks from Start Date & Progress=50% show Progress% in Green color
IF Project B is in First two weeks from Start Date & Progress BETWEEN 30-35% show Progress% in Yellow color
IF Project B is in First two weeks from Start Date & Progress <30% show Progress% in RED color
IF Project B is in First two weeks from Start Date & Progress=35% show Progress% in Green color
IF Project B is in > two weeks & <= four weeks from Start Date & Progress BETWEEN 55-60% show Progress% in Yellow color
IF Project B is in > two weeks & <= four weeks from Start Date & Progress<55% show Progress% in RED color
IF Project B is in > two weeks & <= four weeks from Start Date & Progress=60% show Progress% in Green color
I created below measure to achieve this, But its not working as expected
Can you please help me with this.
Measure =
Var Progress=SUM('table'[Progress%])
Var A=CALCULATE(Progress, FILTER('table',[Project]="A"))
Var B=CALCULATE(Progress, FILTER('table',[Project]="B"))
Var start_date=MIN('table'[start date])
Var first_sprint= DATE( Year( start_date), Month( start_date), Day(start_date)+14 )
Var second_sprint= DATE( Year( start_date), Month( start_date), Day(start_date)+28 )
Var third_sprint=DATE( Year( start_date), Month( start_date), Day(start_date)+42 )
Var fourth_sprint=DATE( Year( start_date), Month( start_date), Day(start_date)+56 )
Var fifth_sprint=DATE( Year( start_date), Month( start_date), Day(start_date)+70 )
return IF(start_date<=first_sprint && A>0.20 && A<0.25,UNICHAR(128993), //Yellow
IF(start_date<=first_sprint && A=0.25,UNICHAR(128994), //Green
IF(start_date<=first_sprint && A<0.20,UNICHAR(128308), //Red
IF(start_date<=second_sprint && A>0.45 && A<0.5,UNICHAR(128993),
IF(start_date<=second_sprint && A=0.5,UNICHAR(128994),
IF(start_date<=second_sprint && A<0.45,UNICHAR(128308),
IF(start_date<=third_sprint && A>0.70 && A<0.75,UNICHAR(128993),
IF(start_date<=third_sprint && A=0.75,UNICHAR(128994),
IF(start_date<=third_sprint && A<0.70,UNICHAR(128308),
IF(start_date<=fourth_sprint && A>0.95 && A<1,UNICHAR(128993),
IF(start_date<=fourth_sprint && A=1,UNICHAR(128994),
IF(start_date<=fifth_sprint && A<1,UNICHAR(128308),
IF(start_date<=fifth_sprint && A=1,UNICHAR(128994),
IF(start_date<=fourth_sprint && A<0.95,UNICHAR(128308),
IF(start_date<=first_sprint && B>0.20 && B<0.25,UNICHAR(128993),
IF(start_date<=first_sprint && B=0.25,UNICHAR(128994),
IF(start_date<=first_sprint && B<0.20,UNICHAR(128308),
IF(start_date<=fourth_sprint && B>0.95 && B<1,UNICHAR(128993),
IF(start_date<=fourth_sprint && B=1,UNICHAR(128994),
IF(start_date<=fourth_sprint && B<0.95,UNICHAR(128308),
IF(ISBLANK(A)||ISBLANK(B), BLANK())))))))))))))))))))))
Thank you!
Regards,
Ashwini
Hi @ashuaswinireddy,
In fact, your formula include forever true conditions in if statements so they may not works correctly.(compare the current value with a larger number that calculate from current value + number, e.g. 1 < 1+1)
I think you need to add a disconnected calendar table and use its date field as axis of the visual, then you can modify the formula to compare the difference between start date and current calendar date and use the result as condition in if statements:
formula =
VAR A =
CALCULATE ( SUM ( 'table'[Progress%] ), FILTER ( 'table', [Project] = "A" ) )
VAR B =
CALCULATE ( SUM ( 'table'[Progress%] ), FILTER ( 'table', [Project] = "B" ) )
VAR start_date =
MIN ( 'table'[start date] )
VAR currDate =
MAX ( 'DateTable'[Date] )
// template variables
VAR first_sprint = 14
VAR second_sprint = 28
VAR third_sprint = 42
VAR fourth_sprint = 56
VAR fifth_sprint = 70
VAR _red =
UNICHAR ( 128308 )
VAR _yellow =
UNICHAR ( 128993 )
VAR _green =
UNICHAR ( 128994 )
// datediff betwen current table date and startdate
VAR diff =
DATEDIFF ( start_date, currDate, DAY )
RETURN
IF (
ISBLANK ( A ) || ISBLANK ( B ),
BLANK (),
IF (
diff <= first_sprint,
IF (
AND ( A > 0.20, A < 0.25 )
|| AND ( B > 0.20, B < 0.25 ),
_yellow,
IF ( A = 0.25 || B = 0.25, _green, IF ( A < 0.20 || B < 0.20, _red ) )
),
IF (
diff <= second_sprint,
IF (
A > 0.45
&& A < 0.5,
_yellow,
IF ( A = 0.5, _green, IF ( A < 0.45, _red ) )
),
IF (
diff <= third_sprint,
IF (
A > 0.70
&& A < 0.75,
_yellow,
IF ( A = 0.75, _green, IF ( A < 0.70, _red ) )
),
IF (
diff <= fourth_sprint,
IF (
AND ( A > 0.95, A < 1 )
|| AND ( B > 0.95, B < 1 ),
_yellow,
IF ( A = 1 || B = 1, _green, IF ( A < 0.95 || B < 0.95, _red ) )
),
IF ( diff <= fifth_sprint, IF ( A < 1, _red, IF ( A = 1, _green ) ) )
)
)
)
)
)
Regards,
Xiaoxin Sheng
Hi @v-shex-msft
Thank you so much for your response and taking time to answer this.
I tried to use the above measure in my report but the above measure is not returning any values for me.
I added disconnected calendar table to my report and used it in Var currDate.
VAR currDate = MAX ( 'DateTable'[Date] )
I need to show this data in Matrix visual and will not be able to use Calender table date field as axis of the visual.
Please find the below screenshot for your reference
Thank you!
Regards,
Ashwini
HI @ashuaswinireddy,
I check the snapshot you shared but not found you use the calendar date fields in it. If the calendar date fields did not used in your visual as axis or category, the formula can't get the corresponding values to calculate. (this is why the formula get blank result, current table row context does not include match ranges in formula conditions)
If these date fields can't use in your matrix, you can try to use the following formulas. I modify your variable to add allselected function to extract the minimum 'start date' based on current category group and use it to compare with the current row context date:
formula =
VAR A =
CALCULATE ( SUM ( 'table'[Progress%] ), FILTER ( 'table', [Project] = "A" ) )
VAR B =
CALCULATE ( SUM ( 'table'[Progress%] ), FILTER ( 'table', [Project] = "B" ) )
VAR start_date =
CALCULATE (
MIN ( 'table'[start date] ),
ALLSELECTED ( 'table' ),
VALUES ( 'table'[Project] ),
VALUES ( 'table'[Summary] )
)
VAR currDate =
MAX ( 'table'[start date] )
// template variables
VAR first_sprint = 14
VAR second_sprint = 28
VAR third_sprint = 42
VAR fourth_sprint = 56
VAR fifth_sprint = 70
VAR _red =
UNICHAR ( 128308 )
VAR _yellow =
UNICHAR ( 128993 )
VAR _green =
UNICHAR ( 128994 )
// datediff betwen current table date and startdate
VAR diff =
DATEDIFF ( start_date, currDate, DAY )
RETURN
IF (
ISBLANK ( A ) || ISBLANK ( B ),
BLANK (),
IF (
diff <= first_sprint,
IF (
AND ( A > 0.20, A < 0.25 )
|| AND ( B > 0.20, B < 0.25 ),
_yellow,
IF ( A = 0.25 || B = 0.25, _green, IF ( A < 0.20 || B < 0.20, _red ) )
),
IF (
diff <= second_sprint,
IF (
A > 0.45
&& A < 0.5,
_yellow,
IF ( A = 0.5, _green, IF ( A < 0.45, _red ) )
),
IF (
diff <= third_sprint,
IF (
A > 0.70
&& A < 0.75,
_yellow,
IF ( A = 0.75, _green, IF ( A < 0.70, _red ) )
),
IF (
diff <= fourth_sprint,
IF (
AND ( A > 0.95, A < 1 )
|| AND ( B > 0.95, B < 1 ),
_yellow,
IF ( A = 1 || B = 1, _green, IF ( A < 0.95 || B < 0.95, _red ) )
),
IF ( diff <= fifth_sprint, IF ( A < 1, _red, IF ( A = 1, _green ) ) )
)
)
)
)
)
Regards,
Xiaoxin Sheng
Thank you for the updated measure!
The problem was with blank() condition..If I remove below blank condition, it is returning values, but all of them are red irrespective of the above if conditions.
IF ( ISBLANK ( A ) || ISBLANK ( B ), BLANK ()
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |