Trying to add filters in DAX formula

[sgajjar]

Hi there, 

I have been working on a report where it shows up/down week over week. I followed this procedure and it worked perfectly fine.

---------------

Rank = RANKX(ALL(Table),CALCULATE( MAX(Table[DATE])) ,,ASC,Dense)

---------------

Icon Measure =
var _previous= CALCULATE(SUM(Table[ACV]),FILTER(ALL(Table),Table[Products]=MAX(Table[Products]) && [Rank]=MAXX(Table,[Rank])-1))
var _curr= CALCULATE(SUM(Table[ACV]),ALLEXCEPT(Table,Table[DATE],Table[Products]))
return IF(_previous<>BLANK(), SWITCH(TRUE(), _previous>_curr,"TriangleLow",_previous=_curr,"TriangleMedium",_previous<_curr,"TriangleHigh"))

---------------

And then I followed this - 

It works perfectly fine if I don't apply any filters to my report. However, if I apply filters to my report - the up/down icons still showing based on the overall values. 
 
I believe I need to add necessary filters in my DAX formula for 'var _previous' and 'var _curr'. However, I am just not sure how to add it since I am not very familiar with DAX functions. 
 
I have two other columns - 'stages' and 'type' . I want to apply filter in dax functions
where stages = 'Live', 'Open', 'Scheduled' And  Type = 'Call', 'Email' 

 
How do I add these filters in DAX formula so Icons shows the correct values for each product with filtered values? 

Please let me know if this isn't the DAX filter formula issue and something else. 
 
Appreciate all your help!

[MFelix]

Hi @sgajjar ,

the issue with your formula is the use of the ALL in the rank measure, since you are making use of all no matter what selection you use the ranking will always be based on the full table and not only on the selection change your rank measure to:

Rank = RANKX(ALLSELECTED(Table),CALCULATE( MAX(Table[DATE])) ,,ASC,Dense)

This should be responsive to the filter you have.

[sgajjar]

@MFelix  - I tried and now I don't see any icon to my report. And I guess my rank measure is working fine. Showing rank week over week, it's just the icons based on the filter values. 

[MFelix]

@sgajjar ,

Try to change the all on your variable to ALLSELECTED also:

Icon Measure =
var _previous= CALCULATE(SUM(Table[ACV]),FILTER(ALLSELECTED(Table),Table[Products]=MAX(Table[Products]) && [Rank]=MAXX(Table,[Rank])-1))
var _curr= CALCULATE(SUM(Table[ACV]),ALLEXCEPT(Table,Table[DATE],Table[Products]))
return IF(_previous<>BLANK(), SWITCH(TRUE(), _previous>_curr,"TriangleLow",_previous=_curr,"TriangleMedium",_previous<_curr,"TriangleHigh"))

[sgajjar]

@MFelix  - Thank you for quick response. It did not work 😞 

[MFelix]

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[sgajjar]

Hi @MFelix  - I have added a sample workbook on this link. This is just a sample data I created but similar fields and filters i am looking for. - https://drive.google.com/file/d/13JOP5NeUJVgbmND9BbVKrpDkq9yP6hgT/view?usp=sharing

Issue - If you add filter on TYPE and remove 'new'. You will still see latest week shows  'TriangleMedium' icon based for Product C, eventhough the values is increasing from 9000 to 20,000.  So basically un/down icon is working if i don't add any filters but it's not working if I add any filters in the report. 

Let me know if you need anything else from my side. Really appreciate your help on this. 

[MFelix]

Hi @sgajjar ,

Is asking for a password to access the file can you unlock it please.

[sgajjar]

@MFelix  Apologies. Please try this link - https://drive.google.com/file/d/13JOP5NeUJVgbmND9BbVKrpDkq9yP6hgT/view?usp=sharing

[MFelix]

Hi @sgajjar,

Looking at your data believe that you only need to add the ALLSELECTED function on your Icon measure so the calculation will be like this:

Icon Measure1 =
VAR _previous =
    CALCULATE (
        SUM ( Sampelfile[ACV] ),
        FILTER (
            ALLSELECTED ( 'Sampelfile' ),
            Sampelfile[Products] = MAX ( 'Sampelfile'[Products] )
                && [Rank1]
                    = MAXX ( 'Sampelfile', [Rank1] ) - 1
        )
    )
VAR _curr =
    CALCULATE (
        SUM ( Sampelfile[ACV] ),
        ALLEXCEPT ( Sampelfile, Sampelfile[DATE], Sampelfile[Products] )
    )
RETURN
    IF (
        _previous <> BLANK (),
        SWITCH (
            TRUE (),
            _previous > _curr, "TriangleLow",
            _previous = _curr, "TriangleMedium",
            _previous < _curr, "TriangleHigh"
        )
    )

Has you can see the values are filled, on the top image you have the values with the ALL option on the bottom with the change of the ALLSELECTED.

Overall:

With the ALL:

With the ALLSELECTED:

File attach.

[sgajjar]

@MFelix  - Check the last screenshot in your message - 

Product D on 04/08/2022 = $4234 with filters Stage= lost and type = new 

Product D on 04/15/2022 = $800  with filters Stage= lost and type = new 

Technically the value is lower in 04/15/2022 but it is showing upword(green) icon . 

[MFelix · Accepted solution]

Hi  , 

Your issue is regarding the curr part of your calculation, has you can see using the values you have on the current you don't get the correct values:

The two should present 800 however the value is much different.

If you redo your measure to:

Icon Measure1 =
VAR _previous =
    CALCULATE (
        SUM ( Sampelfile[ACV] ),
        FILTER (
            ALLSELECTED ( 'Sampelfile' ),
            Sampelfile[Products] = SELECTEDVALUE ( 'Sampelfile'[Products] )
                && [Rank1]
                    = MAXX ( 'Sampelfile', [Rank1] ) - 1
        )
    )
VAR _curr =
    CALCULATE ( SUM ( Sampelfile[ACV] ) )
RETURN
    IF (
        _previous <> BLANK (),
        SWITCH (
            TRUE (),
            _previous > _curr, "TriangleLow",
            _previous = _curr, "TriangleMedium",
            _previous < _curr, "TriangleHigh"
        )
    )

The result will appear correctly:

[sgajjar]

@MFelix  - It worked! Really, really appreciate all your time and your help. This was a very crucial report and we had a timeline for it by the end of this month. Thank you so much for being so active on this issue. 

[sgajjar]

@MFelix  - Just wanted to check, did you get a chance to look into this issue? 

[MFelix]

Hi @sgajjar,

This was a long weekend here with a public holiday today. I will check it tomorrow and get back to you ASAP.