Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
This seems so simple, yet it's so frustrating. Any help would be welcome, please.
So, I have this table, 'Sales'. For simplicity' sake, it has three columns:
- Account
- Has Apples: Y/N
- Has Oranges: Y/N
One Account can have Apples, Orange, both, or none.
Account | Has Apples | Has Oranges |
A | N | N |
B | Y | Y |
C | Y | N |
D | N | Y |
e | N | Y |
To calculate the number of Accounts with oranges, easy, I did get the right total of 3 using this formula:
= Calculate(DistinctCount[Account], Sales[Has Oranges] = "Y")
However, and this is where my problem lies, I want to also show this total of 3 when the 'Has Apples' filter is in the page set to 'Y'.
I tried REMOVEFILTERS(Has Apples). I just got a smaller number, 2, so the difference between one filter and the other. I tried ALL(Has Apples). Nothing.
If I put the top formula in a card and just disconnect it to the 'Has Apples' filter, I get 3. But I really need it in a table that is affected by it (the end result is a %)...
Solved! Go to Solution.
@AliceW So I mocked this up and it seems to work OK but I'm guessing there are additional things going on that may impact the solution. See attached PBIX below signature.
It's crazy, I just get blanks. If I use ALL, it DOES work, but I have other columns in this table I need to use.
@AliceW What about substituting ALL with ALLSELECTED?
@AliceW It is possible that you may run into an issue with Auto exist but try this:
Measure =
COUNTROWS(
DISTINCT(
SELECTCOLUMNS(
FILTER( ALL('Table'), [Has Oranges] = "Y" ),
"__Account",
[Account]
)
)
)
@AliceW So I mocked this up and it seems to work OK but I'm guessing there are additional things going on that may impact the solution. See attached PBIX below signature.
Thank you so much, Greg!
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |