cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper V

## Help with DAX formula to COUNT() or DISCTINTCOUNT() based on condition

Hello to all,

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

 Column1 ABC DEF GHI ABC XYZ DEF DEF XYZ XYZ JKL

Output would be:

Unique values = 4

Values equal to "XYZ" = 3

Thanks for any help.

1 ACCEPTED SOLUTION
Super User

Hi,

In the second formula, replace COUNT with COUNTA.

Regards,
Ashish Mathur
http://www.ashishmathur.com
6 REPLIES 6
Super User

There can be better way but try

```cal1 =
var 1 _v1 = calculate(DISTINCTCOUNT(Table1[Column1]),Table1[Column1] <> "XYZ")

var 1 _v2 = calculate(COUNT(Table1[Column1]),Table1[Column1] = "XYZ")

Return V1+v2```
Helper V

I've tried in this way following your code:

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

But is not working as you suggest nor adding the IF(),  I get numeric values for the part "calculate(DISTINCTCOUNT(...))" but I get error for the part " calculate(COUNT(...))"

`This formula is invalid or incomplete: 'Calculation error in measure 'Table1'[Column1]: The function COUNT takes an argument that evaluates to numbers or dates and cannot work with values of type String.'.`
Super User

Hi,

In the second formula, replace COUNT with COUNTA.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper V

@Ashish_Mathur wrote:

Hi,

In the second formula, replace COUNT with COUNTA.

Thanks for the suggestion. That was the issue, COUNTA instead of COUNT.

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

I couldn't reproduce your issue, the calculate column works well on my side. Could you please share your sample data for further test?

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors