cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rsbin
Post Patron
Post Patron

Measure using Virtual Table

Good Morning,

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

rsbin_0-1616506938375.png

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.

rsbin_1-1616507245319.png

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,

 

 

 

 

 

 

1 ACCEPTED SOLUTION
DataZoe
Super User II
Super User II

@rsbin You could try this measure:

 

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

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

4 REPLIES 4
sayaliredij
Solution Supplier
Solution Supplier

For Virtual Table you can try following DAX

 

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

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
Super User II
Super User II

@rsbin You could try this measure:

 

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

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors