Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello. I am trying to figure out how to make a measure to count the number of times a specific text appears in a column based on a Unique ID
Would look like this:
ID | Text |
2 | Apple |
3 | Bannana |
1 | Apple |
2 | Bannana |
3 | Kiwi |
2 | Bannana |
3 | Apple |
1 | Bannana |
When I wanted to count say "Bannna' it would give me 3 -- it would not count banana twice for the same ID. Apple would give me 3 and Kiwi would give me 1.
Solved! Go to Solution.
I am getting 3 for Bannana with this DAX Measure :
CountTextById = Calculate (
COUNTROWS ( VALUES ( 'Fruits'[ID] ) ),
FILTER(ALL('Fruits'), 'Fruits'[Text] = "Bannana")
)
Tell me if it works for you.
Yes but if you select a [Text] from the slicer it will give you the right number. Similirly if you place [Text] along with this measure in a table visual it should give the correct count for each text. If you are interested in summing these counts you can use
=
SUMX (
VALUES ( 'Table'[Text] ),
COUNTROWS ( CALCULATETABLE ( VALUES ( 'Table'[ID] ) ) )
)
This will give you 3 for banana, 3 for apple, 1 for kiwi and 7 for total. The total will change depending on the selected [Text] values. For example if you select "banana" and "kiwi" the total would be 4. You'll get the same number when using a card visual.
That doesn't give me the count based on the text criteria. That is giving me the total unique IDs of 3. I want to look at how many times a text criteria exists and only count it once per ID in the table.
Yes but if you select a [Text] from the slicer it will give you the right number. Similirly if you place [Text] along with this measure in a table visual it should give the correct count for each text. If you are interested in summing these counts you can use
=
SUMX (
VALUES ( 'Table'[Text] ),
COUNTROWS ( CALCULATETABLE ( VALUES ( 'Table'[ID] ) ) )
)
This will give you 3 for banana, 3 for apple, 1 for kiwi and 7 for total. The total will change depending on the selected [Text] values. For example if you select "banana" and "kiwi" the total would be 4. You'll get the same number when using a card visual.
I am getting 3 for Bannana with this DAX Measure :
CountTextById = Calculate (
COUNTROWS ( VALUES ( 'Fruits'[ID] ) ),
FILTER(ALL('Fruits'), 'Fruits'[Text] = "Bannana")
)
Tell me if it works for you.
User | Count |
---|---|
42 | |
27 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |