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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Trying to add filters in DAX formula

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 - 

sgajjar_0-1650641449005.png

 

 

 

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!

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

MFelix_0-1651052353998.png

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:

MFelix_1-1651052433018.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

14 REPLIES 14
MFelix
Super User
Super User

Hi @Anonymous ,

 

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@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. 

 

@Anonymous ,

 

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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. 

Hi @Anonymous ,

 

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi @Anonymous,

 

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:

MFelix_0-1650994340558.png

With the ALL:

MFelix_1-1650994406031.png

With the ALLSELECTED:

 

MFelix_2-1650994442437.png

MFelix_3-1650994479408.png

 

File attach.

 

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@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 . 

 

sgajjar_0-1650999177738.png

 

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:

MFelix_0-1651052353998.png

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:

MFelix_1-1651052433018.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@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. 

Anonymous
Not applicable

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

 

Hi @Anonymous,

 

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.