Measure using Virtual Table

[rsbin]

Good Morning,

I find myself stuck and could use some assistance please.  I have the following data table.

User will select a date range normally 2 - 4 weeks.  For the date range selected and then for each Location, Auditor, and Test, I need to determine if Sum of Fails > 0.  I went down the path of trying to create a virtual table:

Failed = 
    Var TempTable = SUMMARIZECOLUMNS( CPReportSummary[LocationCode], 
                    CPReportSummary[AuditorName], CPReportSummary[CP Test], 
                    "Fails", SUMX(CPReportSummary, CPReportSummary[Fail] )) 
     Return



 

I have tried both SUM and SUMX, but I get stuck on the Return statement. Final table visual should look something like this.

Count of CP Test is simply a DistinctCount on the original table.  The Failed should be the count of unique Test Numbers where sum of fails > 0.

If there is a better approach than the virtual table approach, I am welcome to any and all suggestions.

Many thanks and kind regards,

[sayaliredij]

For Virtual Table you can try following DAX

Failed = SUMMARIZECOLUMNS(Data[LocationCode],Data[AuditorName],Data[Cp Test],"FailCount", SUM(Data[Fail]))

[rsbin]

Hello @sayaliredij 

Appreciate the response to my question.  However, I think @DataZoe has provided a much easier statement that looks to be working.  

Thanks again and best regards,

[DataZoe · Accepted solution]

@rsbin You could try this measure:

Failed = CALCULATE(DISTINCTCOUNT(CPReportSummary[CP Test]),CPReportSummary[Fail]>0)

[rsbin]

@DataZoe 

Thanks for the prompt response.  I have done a quick test, but thus far, it looks like your solution works....and much easier.  I thought I was making it more complex than I needed it to be.   More testing on my side is needed though.

Thanks again and kudos!