Create filter using formula MAX number

[Noredlac86]

Hi Team.

I have this visual chart in powerbi:

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:

 
Its working fine 🙂

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:

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:

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!

[Anonymous]

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.

3. Second filtering..

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]

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.

3. Result:

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

[Noredlac86]

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

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

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!