Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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!
Solved! Go to 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:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em Português@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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCan 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix Apologies. Please try this link - https://drive.google.com/file/d/13JOP5NeUJVgbmND9BbVKrpDkq9yP6hgT/view?usp=sharing
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:
With the ALL:
With the ALLSELECTED:
File attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@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 .
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:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@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.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsStarting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
81 | |
70 | |
49 |
User | Count |
---|---|
143 | |
124 | |
107 | |
60 | |
55 |