Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I am attempting to create a Slicer to toggle between the current values and the day over day change.
The Date Rank column ranks the days from 1 (most recent) descending. The curr_date variable I have created is to use the most recent day (each table in the visualization is only ever filtered to one day) and the prev_date is shifted to the next most recent day by adding 1 as shown below
var curr_date = min(NG_POS_MGR[Date Rank])
var prev_date = (min(NG_POS_MGR[Date Rank]) + 1)
var switchvalue =
SWITCH(
SELECTEDVALUE('Toggle Table'[ID])
,1, calculate(sum(NG_POS_MGR[Delta]), NG_POS_MGR[Date Rank] = curr_date)/10000
,2, (calculate(sum(NG_POS_MGR[Delta]), NG_POS_MGR[Date Rank] = curr_date)/10000) - (calculate(SUM(NG_POS_MGR[Delta]), NG_POS_MGR[Date Rank] = prev_date))/10000
, calculate(sum(NG_POS_MGR[Delta]), NG_POS_MGR[Date Rank] = curr_date)/10000
)
return switchvalue
However, when I use this in the visualizations, the current day values always appear properly, but the day over day change that I am trying to calculate do not. I will either get empty tables or I will get the alternate result, which in this case is just the summed value again.
I would greatly appreciate any help on this, thanks
Solved! Go to Solution.
@Anonymous here is the measure provided as solution by email.
Selected Value =
var curr_date_rank = min(Sheet3[Date Rank])
var prev_date_rank = (Min(Sheet3[Date Rank]) + 1)
var switchvalue =
SWITCH( SELECTEDVALUE(Query1[ID]),
1, calculate(sum(Sheet3[Delta]), Sheet3[Date Rank] = curr_date_rank)/10000 ,
2, calculate(sum(Sheet3[Delta]), Sheet3[Date Rank] = curr_date_rank)/10000 - calculate(SUM(Sheet3[Delta]), ALL(Sheet3[Date]) ,Sheet3[Date Rank] = prev_date_rank)/10000, 0)
return
switchvalue
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous it should work, how you are trying to visualize the data. Can you return prev_date value from the measure to check if you are getting correct value if yes, then just return calculate expression for prev day and see you are getting value.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
I think this may be my issue, I am getting values but I am getting 1's where I thought I would just be getting null values. Ideally, this table would just be 2's and null values
@Anonymous I'm not surely if you resolved your issue or still need help.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
I am finding that my issue is actually filtering in the visualization. I am filtering using the date column but this seems to be filtering out the values from the previous day, so I am just getting the same value as the sum. I can filter by the date rank column and get the correct values for the difference but I cannot seem to get it to work when using the date column
I have changed my code with the thought that using the date instead of the date rank will yield the correct answer to no avail
Selected Value =
var curr_date_rank = min(NG_POS_MGR[Date Rank])
var prev_date_rank = (Min(NG_POS_MGR[Date Rank]) + 1)
var curr_date = CALCULATE(min(NG_POS_MGR[Date]), NG_POS_MGR[Date Rank] = curr_date_rank)
var prev_date = CALCULATE(min(NG_POS_MGR[Date]), NG_POS_MGR[Date Rank] = prev_date_rank)
var switchvalue =
SWITCH(
SELECTEDVALUE('Toggle Table'[ID])
,1, calculate(sum(NG_POS_MGR[Delta]), NG_POS_MGR[Date] = curr_date)/10000
,2, calculate(sum(NG_POS_MGR[Delta]), NG_POS_MGR[Date] = curr_date)/10000 - calculate(SUM(NG_POS_MGR[Delta]), NG_POS_MGR[Date] = prev_date)/10000
, 0
)
return switchvalue
@Anonymous it shoul be straight forward, can you share sample pbix file, you can remove sensitive information before sharing.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Sent!
Hi @Anonymous ,
Can you upload your .pbix file to onedrive business and share the link with me?
@Anonymous here is the measure provided as solution by email.
Selected Value =
var curr_date_rank = min(Sheet3[Date Rank])
var prev_date_rank = (Min(Sheet3[Date Rank]) + 1)
var switchvalue =
SWITCH( SELECTEDVALUE(Query1[ID]),
1, calculate(sum(Sheet3[Delta]), Sheet3[Date Rank] = curr_date_rank)/10000 ,
2, calculate(sum(Sheet3[Delta]), Sheet3[Date Rank] = curr_date_rank)/10000 - calculate(SUM(Sheet3[Delta]), ALL(Sheet3[Date]) ,Sheet3[Date Rank] = prev_date_rank)/10000, 0)
return
switchvalue
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
102 | |
99 | |
38 | |
37 |
User | Count |
---|---|
158 | |
125 | |
76 | |
74 | |
63 |