Forum Discussion

Noredlac86's avatar
Noredlac86
Helper I
3 years ago

Create filter using formula MAX number

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!

 

 




Need Help
Tips and Tricks

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Noredlac86
      Helper I
      3 years ago

      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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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