COUNTIF to check for distinct values in DAX

Question

I really need this formula in DAX but I really don't know how to build it:

= COUNTIF(<my_raing>,
     INDEX(<my_raing>,
         MATCH(FALSE, ISBLANK(<my_raing>), 0)
     )
  ) = COUNT(<my_raing>)

My real formula is this:

= COUNTIF(
    Table2[@[1]:[11]],
    INDEX(
        Table2[@[1]:[11]],
        MATCH(FALSE, ISBLANK(Table2[@[1]:[11]]), 0)
     )
  ) = COUNT(Table2[@[1]:[11]])

The range is 11 consecutive cells from only one row. The example set of data is:

Sample Data

And the formula returns only TRUE or FALSE when all available (not blank) values are equal each other (it is enough to compare only one with all)

UnPivot data set seems like this:

UnPivot Data

Start by detailing exactly what your excel formula does, once you understand the logic then proceeding should not be too hard. — Solar Mike
Hi Mike, you are right and I'm sorry. I edited the question and you can see what is the goal. Thanks! — StanYan
May be relevant: stackoverflow.com/questions/54442875/power-bi-countif/… — Alexis Olson
DAX doesn't work well with pivoted data tables. I strongly recommend unpivoting the 1 - 11 columns. — Alexis Olson
@AlexisOlson - only COUNTIF in not enough in my case. And the set of data is huge and if I unpivot it will multiply it. OK if I do that then how can I implement my formula? :) — StanYan

Marmiketin Marmiketin · Accepted Answer · 2020-04-06T17:34:44

Not done in DAX but may be useful for other on here.

You can use this formula in column 12 (which I'm assuming is column L) =IF(MIN(A1:K1)=MAX(A1:K1), "TRUE", "FALSE")

COUNTIF to check for distinct values in DAX

1 Answers