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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Switch Selected Value Errors

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

1 ACCEPTED 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.

View solution in original post

8 REPLIES 8
parry2k
Super User
Super User

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

Anonymous
Not applicable

 vis.PNG

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.

Anonymous
Not applicable

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.

Anonymous
Not applicable

Sent!

Hi  @Anonymous ,

 

Can you upload your .pbix file to onedrive business and share the link with me?

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a 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.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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