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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Noredlac86
Helper I
Helper I

Create filter using formula MAX number

Hi Team.

I have this visual chart in powerbi:

Noredlac86_5-1676902717233.png

 

And i want to show only the last month and the data with the "Sex=F". Therefore i have created a simple filter:

LastMonth = MAX(MyTable[ReportNo])

and use this filter in the chart:

 
Noredlac86_6-1676902845122.png

 
Its working fine 🙂

 

Noredlac86_9-1676903081741.png

 

 

BUt the problem that i have. Its when the original visual chart, does not have any "Sex=F" in the last month, for example if i have this:

Noredlac86_4-1676902689189.png

If apply the same filter, should be show the table empty, because we dont have any (Sex=F) in the reportNo=6, howerver the result is this one:

Noredlac86_8-1676903005623.png

Looks like powerbi, make first the filter Sex=F, and after that make the filter LastMonth = MAX(MyTable[ReportNo]), howerve is wrong, cause i need that the filter should be: ReportNo= 6 (Its the last report available) and Sex=F


Do you know how can fixed it?

Thanks for any comment or idea!

 

 




3 REPLIES 3
Anonymous
Not applicable

Hi  @Noredlac86 ,

 

Here are the steps you can follow:

1. Create measure.

 

If you are doing the filtering in two parts.

Flag1 =
var _max=MAXX(
    ALL('Table'),[ReportNo])
return
IF(
    MAX('Table'[ReportNo])= _max ,1,0)
Flag2 =
IF(
    [Flag1]=1&&MAX('Table'[Sex])="F",1,0)

2. First filter.

vyangliumsft_0-1677115486326.png

3. Second filtering..

vyangliumsft_1-1677115486330.png

 

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Anonymous
Not applicable

Hi  @Noredlac86 ,

 

Here are the steps you can follow:

1. Create measure.

Flag =
var _max=MAXX(
    ALL('Table'),[ReportNo])
return
IF(
    MAX('Table'[ReportNo])= _max && MAX('Table'[Sex]) ="F",1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

vyangliumsft_0-1677052766936.png

3. Result:

vyangliumsft_1-1677052766941.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

HI @Anonymous 
Thanks so much for your time and reply my message, i was checking your message, and if this is the original table:

 

Noredlac86_0-1677070595325.png

The results should be empty. Cause the result should me math the following two filters:
(1) Last report available: In this case should be 6.
and with the users that match with the ReportNo=6, should be show, (2) Users with sex=F. 


In that case, if we apply the filter manually, first filter show this (ReportNo=6):

Noredlac86_1-1677070781452.png


And for that table apply second filter. Sex=F; Therefore the last result should be empty.

I was wondering if the formula that you share with me, works with that logical.

 

thansk again!

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors