- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Power Platform Integration - Better Together!
- Power Platform Integrations (Read-only)
- Power Platform and Dynamics 365 Integrations (Read-only)
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

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.

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Re: Filter and Show Values in Matrix based on Cond...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Filter and Show Values in Matrix based on Condition

01-22-2023
06:28 PM

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

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-24-2023
06:08 PM

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.

If this post

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-25-2023
03:29 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-27-2023
12:53 AM

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.

If this post

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-29-2023
09:32 AM

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

Announcements

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.

Featured Topics

Top Solution Authors

User | Count |
---|---|

108 | |

96 | |

75 | |

62 | |

55 |

Top Kudoed Authors

User | Count |
---|---|

138 | |

102 | |

89 | |

86 | |

64 |