A strange problem with measures and DAX

[awakened]

Hi

Ran into a strange problem with creating measures and using DAX.

I created measures that count totals of "OK" in columns. I then created a measure out of those measures to get the total number of "OK" from left to right in the data table. (Will add screenshot).

The problem I'm running into is that for some rows it is calculating the "OK" properly, and for others it is not. For example, it's calculating only one "OK" but You can clearly see that there are more.

What confuses me is that if there is a problem in the formula it should show problems in other calculations or other row calculations but it's not doing that and a lot of rows are displayed correctly.

The DAX in the measures:

2.1 OK count =

CALCULATE(COUNTROWS(blabla_something, blabla_something[column_name1] = "OK") - this actually calculates properly, shows 1 when I display it.

The problem starts here:

TOTAL OK count = CALCULATE(blabla_something, blabla_something[OK_count_column_name1]+(blabla_something, blabla_something[OK_count_column_name2]... so on and so forth, all same - and again, it calculates some rows fine, but not all. Can someone point me in the right direction?

Thanks!

[m3tr01d]

Hi @awakened ,

For me, the definition you have for TOTAL OK count measure doesn't make sense, can you give us the real DAX expression?

[awakened]

I add 22 measures together.

TOTAL OK count = CALCULATE(table_name[2.1 OK count]+table_name[2.2 OK count]+table_name[2.3 OK count]+table_name[3.1 OK count]+table_name[3.2 OK count]+table_name[3.3 OK count]+table_name[3.4 OK count]+table_name[3.5 OK count]+table_name[3.6 OK count]+table_name[3.7 OK count]+table_name[3.8 OK count]+table_name[4.1 OK count]+table_name[4.2 OK count]+table_name[5.1 OK count]+table_name[6.1 OK count]+table_name[7.1 OK count]+table_name[8.1 OK count]+table_name[9.1 OK count]+table_name[10.1 OK count]+table_name[10.2 OK count]+table_name[11.1 OK count]+table_name[11.2 OK count])

[Whitewater100]

Hi:

This is a little tedious but can work. This example is for counting yes. 

Count Yes's = IF(AND('Table'[Column1] = "Yes", 'Table'[Column2] = "Yes"),2,IF(OR('Table'[Column1] = "Yes", 'Table'[Column2] = "Yes"),1))

You have more combinations, so accounting for all of these will just take some extra planning.

You can also unpivot all those yes columns so all the answers (OK) are stacked and then it is much easier to sum up that column.

I hope this helps.

[awakened]

Thanks.

Already went for the unpivot column solution. Doing my best to make it work. If it doesn't will try Your solution.

[Whitewater100]

Great, please let me know if it ecomes your solution! Thanks..

[awakened · Accepted solution]

Ok so learning every day.

It turns out that my initial calculation and measure actually worked on a visual.

It broke in my table visual which was confusing but I figured it out. The reason was that I had too few measures in the table. When I added more or added my calculated measures that I shared in previous posts = everything worked.

So the formula:

2.1 OK count =

CALCULATE(COUNTROWS(blabla_something, blabla_something[column_name1] = "OK") - this actually calculates properly, shows 1 when I display it. = this works and adding all columns together through different measures after:

TOTAL OK count = CALCULATE(table_name[2.1 OK count]+table_name[2.2 OK count]+table_name[2.3 OK count]+table_name[3.1 OK count]+table_name[3.2 OK count]+table_name[3.3 OK count]+table_name[3.4 OK count]+table_name[3.5 OK count]+table_name[3.6 OK count]+table_name[3.7 OK count]+table_name[3.8 OK count]+table_name[4.1 OK count]+table_name[4.2 OK count]+table_name[5.1 OK count]+table_name[6.1 OK count]+table_name[7.1 OK count]+table_name[8.1 OK count]+table_name[9.1 OK count]+table_name[10.1 OK count]+table_name[10.2 OK count]+table_name[11.1 OK count]+table_name[11.2 OK count]) = this also works but have to be careful how and with what You display it with. Can break if some other measure is missing.