DistinctCount with a condition in PowerPivot

0
votes

I have a distinctcount in a PowerPivot on a column "Pers ID". I would like to add a condition on this distinct count: only DistinctCount when in Column "Month" the month is later or equal to November (example). I tried to look for a solution on the forum and came out with this:

CALCULATE(DISTINCTCOUNT('Alles 24_07_21'[Pers_ID]);'Alles 24_07_21'[Month_N]>=11)

If I try this I get the following error message:

Calculation error in measure 'Alles 24_07_21'[Distinct Count]: DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

I hope somebody can help me out. Does this formula makes any sense in the first place? What can I do to avoid the error? Column "Pers ID" is formatted in the source file as a number and Month as a number a well.

Pers_ID Month_N
643328 11
643329 11
643328 10
643329 12

For example...in the table above, the DistinctCount should be 0 for Pers_ID number "643328" because it has a Month_N < 11. The DistinctCount for Pers_ID "643328" should be 1 because it has Month_N >= 11. I also have a few filters on the Pivot Table but I don't think that is a problem.

Thx a lot in advance!

Best regards,

Nick

1
exceldaxpowerquerypowerpivot
Can you please provide sample data How To. Also, is the data type of Month_N is integer? – smpa01
I tried to add what you requested. Data type in the source file are for both columns "number". Thx a lot in advance! – Nick Weerts

1 Answers

0
votes

The formula seems fine but it appears to be a data type error. It doesn't matter as much what the source file data type is as long as it's correct in your Power Pivot model. From the error message, it would seem that Month_N column is typed as text in your model.

One way to test this would be to try replacing 11 with "11" in your measure. If you don't get the same error, then this is very likely the culprit.