Forum Discussion
Advocate IICOUNT of Values divided by All Rows from Second Table
I am trying to create a measure to generate a percentage that responds to 9 slicers.
The data needs to come from 2 tables which are joined (1 to many) by a 2 field concatenated key.
The numerator needs to be a count of rows with status (Status column = A or B) from table 1. This table is about 43K rows.
The denominator is all rows from table 2 responsive to the slicers. This table is about 150K rows.
Pretty much everything I do evaluates to 100%. I am pretty sure that it is getting the numerator correct and using only the filtered .sliced rows as the denominator.
I'm stuck and looking for any help I can get.
Hi comish4lif2 ,
Based on your description, I have created a simple sample:
Please try something like this:
Measure = DIVIDE(COUNTROWS(FILTER(ALL('Table 1'),[Status]="A"||[Status]="B")),COUNTROWS('Table 2'))Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
SeananSolution Supplier
Hi comish4lif2
If your numerator is worked, having you tried using the ALL function for the denominator? This function is useful for clearing filters. Please see the documentation below.
v-jianboli-msftCommunity Support
Hi comish4lif2 ,
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.
Refer to:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- comish4lif2
No. It has not been solved.
I'm not sure how suggesting a single DAX command would serve as a solution, but in my case, it did not.
- v-jianboli-msft
Hi comish4lif2 ,
Based on your description, I have created a simple sample:
Please try something like this:
Measure = DIVIDE(COUNTROWS(FILTER(ALL('Table 1'),[Status]="A"||[Status]="B")),COUNTROWS('Table 2'))Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.