Forum Discussion

ashuaswinireddy's avatar
ashuaswinireddy
Icon for Helper I rankHelper I
3 years ago

Filter and Show Values in Matrix based on Condition

Hello All,

 

I have below example data set

 

IDProjectSummaryProgress%Start Date
1AAAA100%11/23/2022
2AAAA 11/23/2022
3ABBB80%11/23/2022
4BCC90%11/23/2022
5BDD95%11/23/2022
6BDD100%11/23/2022
7CEE25%1/1/2023
8CFF 1/1/2023
9CGG20%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

 

 

 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      ashuaswinireddy
      Icon for Helper I rankHelper I
      3 years ago

      Hi Anonymous 

       

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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