Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to Solution.
@rsbin You could try this measure:
Failed = CALCULATE(DISTINCTCOUNT(CPReportSummary[CP Test]),CPReportSummary[Fail]>0)
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
For Virtual Table you can try following DAX
Proud to be a Super User!
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,
@rsbin You could try this measure:
Failed = CALCULATE(DISTINCTCOUNT(CPReportSummary[CP Test]),CPReportSummary[Fail]>0)
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |