Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply

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

 

 

 

4 REPLIES 4
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

ashuaswinireddy_0-1674690380271.png

 

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

ashuaswinireddy_0-1674766534448.png

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors