Forum Discussion
Post ProdigyDistinct Stores Count of a functionality
I have a list of States with each of the functionalities at each store (A, B, C) If it equals 1 that means the functionality is there, if it has 0 it means it does not.
I'm looking for the count of Distinct States that have these functionalities. Functionalities A and B are part of a larger process called PHTEST - so looking for the max number between both of those. Then I just need the distinct count of states for process C.
End result needed - 13 States have Process A or B in it's store & 8 States have Process C in their stores.
| PHTEST | |||||||||||||||
| Store | State | A | B | C | |||||||||||
| 3193 | FL | 1 | 1 | 0 | |||||||||||
| 6341 | TN | 0 | 1 | 0 | Row Labels | Sum of A | Row Labels | Sum of B | Row Labels | Sum of C | |||||
| 3912 | AZ | 1 | 1 | 0 | AZ | 4 | AZ | 3 | AZ | 1 | |||||
| 3211 | FL | 1 | 1 | 1 | FL | 4 | FL | 7 | FL | 1 | |||||
| 3220 | TX | 1 | 1 | 0 | IL | 2 | IL | 2 | IL | 1 | |||||
| 3233 | TX | 1 | 0 | 0 | IN | 1 | IN | 1 | IN | 1 | |||||
| 4618 | FL | 1 | 0 | 0 | KY | 0 | KY | 1 | KY | 0 | |||||
| 11977 | WV | 1 | 1 | 1 | LA | 3 | LA | 3 | LA | 1 | |||||
| 4433 | NV | 1 | 0 | 0 | MI | 1 | MI | 1 | MI | 0 | |||||
| 4077 | AZ | 1 | 0 | 0 | NV | 2 | NV | 1 | NV | 1 | |||||
| 5399 | FL | 0 | 1 | 0 | PA | 1 | PA | 1 | PA | 0 | |||||
| 9186 | AZ | 1 | 1 | 0 | TN | 0 | TN | 3 | TN | 0 | |||||
| 3365 | TN | 0 | 1 | 0 | TX | 4 | TX | 3 | TX | 0 | |||||
| 3555 | FL | 0 | 1 | 0 | WI | 1 | WI | 1 | WI | 1 | |||||
| 5382 | LA | 1 | 1 | 0 | WV | 1 | WV | 1 | WV | 1 | |||||
| 3822 | TX | 1 | 1 | 0 | (blank) | (blank) | (blank) | ||||||||
| 5814 | NV | 1 | 1 | 1 | Grand Total | 24 | Grand Total | 28 | Grand Total | 8 | |||||
| 4379 | FL | 0 | 1 | 0 | |||||||||||
| 7712 | KY | 0 | 1 | 0 | 11 Distinct States do not have zero | 13 Distinct State do not have zero | 8 Distinct States do not have zero | ||||||||
| 3399 | LA | 1 | 1 | 1 | |||||||||||
| 3406 | IL | 1 | 1 | 1 | |||||||||||
| 3437 | IN | 1 | 1 | 1 | PHTEST MAX 13 Distinct States | ||||||||||
| 3440 | LA | 1 | 1 | 0 | |||||||||||
| 10204 | FL | 0 | 1 | 0 | |||||||||||
| 3473 | TN | 0 | 1 | 0 | |||||||||||
| 9146 | MI | 1 | 1 | 0 | |||||||||||
| 4344 | AZ | 1 | 1 | 1 | |||||||||||
| 7543 | PA | 1 | 1 | 0 | |||||||||||
| 6593 | TX | 1 | 1 | 0 | |||||||||||
| 3539 | IL | 1 | 1 | 0 | |||||||||||
| 5339 | FL | 1 | 1 | 0 | |||||||||||
| 3617 | WI | 1 | 1 | 1 |
Hi jcastr02
Below DAX would work.PHTest = VAR A_ = CALCULATE ( DISTINCTCOUNT ( 'Table'[State] ), 'Table'[A] = 1 ) VAR B_ = CALCULATE ( DISTINCTCOUNT ( 'Table'[State] ), 'Table'[B] = 1 ) RETURN IF ( A_ > B_, A_, B_ )C_ = Var C_ = CALCULATE(DISTINCTCOUNT('Table'[State]),'Table'[C]=1) Return C_Let me know if you have a different expectation along with some screenshot of expected output and how your table structure is.
If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!
Follow me on LinkedIn!!!
1 Reply
NaveenGandhiMemorable Member
Hi jcastr02
Below DAX would work.PHTest = VAR A_ = CALCULATE ( DISTINCTCOUNT ( 'Table'[State] ), 'Table'[A] = 1 ) VAR B_ = CALCULATE ( DISTINCTCOUNT ( 'Table'[State] ), 'Table'[B] = 1 ) RETURN IF ( A_ > B_, A_, B_ )C_ = Var C_ = CALCULATE(DISTINCTCOUNT('Table'[State]),'Table'[C]=1) Return C_Let me know if you have a different expectation along with some screenshot of expected output and how your table structure is.
If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!
Follow me on LinkedIn!!!