Calc based on dynamic date slicer

[nidhishah]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[nidhishah]

Thanks!!

[Ashish_Mathur]

You are welcome.

[amitchandak]

@nidhishah , Please find the updated file. Used all selected and done few changes based on that

[nidhishah]

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

        )

)

[amitchandak]

@nidhishah , Please find the attcahed file after signature. Created Measure 5 and used it in Measure 3

[nidhishah]

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

[amitchandak]

@nidhishah , Please find attached file after signature.

Measure 3 is the one you are looking for.

Measure and measure2 you can use for validation

[nidhishah]

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]

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? 

[amitchandak]

@nidhishah , Please find the attached file. Created measure 5 and used it measure 3