DAX formula to COUNT() or DISCTINTCOUNT() based on condition?

Question

I want to include a new measure using a DAX formula in a Pivot Table. I have the following DAX formula to count unique values in Column1

= DISTINCTCOUNT(Table1[Column1])

How can I COUNT() values equal to "XYZ" and DISTINCTCOUNT() values different than "XYZ". I've tried something like this but is not working.

=IF(Table1[Column1] <> "XYZ",
     DISTINCTCOUNT(Table1[Column1]),
     COUNT(Table1[Column1])

Sample Input

+----+---------+---------+
| Id | Column1 | Column2 |
+----+---------+---------+
| 1  | ABC     | A       |
+----+---------+---------+
| 1  | DEF     | A       |
+----+---------+---------+
| 1  | GHI     | B       |
+----+---------+---------+
| 1  | ABC     | A       |
+----+---------+---------+
| 1  | IIU     | XYZ     |
+----+---------+---------+
| 1  | HYW     | A       |
+----+---------+---------+
| 1  | ABC     | A       |
+----+---------+---------+
| 1  | ABC     | B       |
+----+---------+---------+
| 1  | WQW     | XYZ     |
+----+---------+---------+
| 1  | ABC     | A       |
+----+---------+---------+
| 1  | RTT     | XYZ     |
+----+---------+---------+
| 1  | PPO     | XYZ     |
+----+---------+---------+

Output I'm looking for:

+----+---+---+-----+
| ID | A | B | XYZ |
+----+---+---+-----+
| 1  | 3 | 2 | 4   |
+----+---+---+-----+

A measure can only return one final number. Which output value do you want to return? — Alexis Olson
@AlexisOlson Hi Alexis. I've updated in my original post with a more appropiated sample input and output I'm looking for. I have a table with several IDs (in this sample I show only ID=1). I'd like to count DISTINT for which Column2="A", DISTICNTCOUTN for which Column2="B" and COUNT values for which Column2 = "XYZ". I hope make sense. Thanks — Ger Cas

Alexis Olson Alexis Olson · Accepted Answer · 2019-10-17T20:52:56

The output table is helpful.

I think you want something like this:

IF (
    SELECTEDVALUE ( Table1[Column2] ) = "XYZ",
    COUNT ( Table1[Column2] ),
    DISTINCTCOUNT ( Table1[Column1] )
)

If you only have one value in your filter context and that value is "XYZ" then you count the occurrences of "XYZ". Otherwise, count the number of distinct Column1 values.

If working with old versions that don't support SELECTEDVALUE try this:

count =
IF (
    IF ( HASONEVALUE ( Table1[Column2] ), VALUES ( Table1[Column2] ) ) = "XYZ",
    COUNT ( Table1[Column2] ),
    DISTINCTCOUNT ( Table1[Column1] )
)

DAX formula to COUNT() or DISCTINTCOUNT() based on condition?

1 Answers