Filter and Show Values in Matrix based on Condition

[ashuaswinireddy]

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

[v-shex-msft]

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

[ashuaswinireddy]

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

[v-shex-msft]

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

[ashuaswinireddy]

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 ()