Forum Discussion
Calc based on dynamic date slicer
Hi,
I have data like the below table.
| Date | Category | Product | Test_result | |||
| 1/1/2021 | A | xyz | Pass | |||
| 1/1/2021 | A | pqr | Fail | |||
| 1/1/2021 | B | abc | Fail | |||
| 1/1/2021 | B | def | Pass | |||
| 1/1/2021 | B | ghi | Pass | |||
| 1/2/2021 | A | xyz | Fail | |||
| 1/2/2021 | A | pqr | Pass | |||
| 1/2/2021 | B | abc | Fail | |||
| 1/2/2021 | B | def | Fail | |||
| 1/2/2021 | B | ghi | Pass | |||
| 1/3/2021 | A | xyz | Fail | |||
| 1/3/2021 | A | pqr | Fail | |||
| 1/3/2021 | B | abc | Pass | |||
| 1/3/2021 | B | def | Pass | |||
| 1/3/2021 | B | ghi | Fail | |||
| 1/4/2021 | A | xyz | Fail | |||
| 1/4/2021 | A | pqr | Pass | |||
| 1/4/2021 | B | abc | Fail | |||
| 1/4/2021 | B | def | Fail | |||
| 1/4/2021 | B | ghi | Pass | |||
| 1/5/2021 | A | xyz | Pass | |||
| 1/5/2021 | A | pqr | Pass | |||
| 1/5/2021 | B | abc | Pass | |||
| 1/5/2021 | B | def | Pass | |||
| 1/5/2021 | B | ghi | Fail |
I need to summarize it into a table visual as below, and there is date slicer on the page
| Category | Product | Final Result | ||
| A | pqr | |||
| A | xyz | |||
| B | abc | |||
| B | def | |||
| B | ghi |
Logic for "Final Result" needs to be as per this:
If (Test_result on max date based on slicer = Pass,
Then Final Result = Great
Else If (Test_result on max date based on slicer = Fail,
Then
if(Test_result = Pass for atleast 1 day in the min to max date range based on the slicer,
Then Final result = Okay,
Else Final result = Bad
)
)
)
)
Any idea how to implement this in PowerBI?
Thanks
Nidhi
11 Replies
Thanks!!
You are welcome.
nidhishah , Please find attached file after signature.
Measure 3 is the one you are looking for.
Measure and measure2 you can use for validation
Thanks @amitchandak ! For measure 2, the code is getting previous day's (max(date)-1). How can i change it to look for any "pass" within the date range in a slicer?
nidhishah , Please find the attached file. Created measure 5 and used it measure 3
Also, @amitchandak the measure is not consiering a dynamic slicer at all. If i move the slicer to pick a date range that doesnt contain the max date in the main data set, then the measures aren't working.
nidhishah , Please find the attcahed file after signature. Created Measure 5 and used it in Measure 3
amitchandak I added a date slicer - which is what i need, and if I pick a date range that doesnt contain 1/5 date, then none of the measures work. I am unable to upload the PBIX file however. Let me know if you can replicate the issue and can help more.
nidhishah , Please find the updated file. Used all selected and done few changes based on that
Thanks amitchandak. I really appeciate you helping.
May be I didnt explain the logic I need for the "Final result" properly. The last file you have is doing the part highlighted in green properly. But the part highlighted in red is not what the measure5 is creating.
For the Red part, what i mean, is if for example, date range selected in the slicer is 1/1 to 1/4, and if the test result for a product is "fail" for 1/4, then I want to see if there is a "pass" for atleast any one date between 1/1 and 1/3 (including 1/1 and 1/3). If so, call it "Okay". I am not sure how to do this part.
If (Test_result on max date based on slicer = Pass,
Then Final Result = GreatElse If (Test_result on max date based on slicer = Fail,
Then
if(Test_result = Pass for atleast 1 day in the min to max date range based on the slicer,
Then Final result = Okay,
Else Final result = Bad
))
)
