substract dax

[juan_pablo]

Hi, 

I'm trying to do a simple thing: just substract one unit (1) from a measure, but when I do this, the rows become all rows of the model as the imagen shows: the result must be 7 and 2 in the TWO rows of the current table, not -1 in all rows of the model.

This is the measure:

Ahorro=COUNT('(5) MR Header Fields'[srnScnNumber])

 

Ahorro=COUNT('(5) MR Header Fields'[srnScnNumber])-1

 

Why this happens and how can I solve it?

Thank you very much.

[Anonymous]

HI @juan_pablo,

Matrix visuals will auto hide blank records(you can right click on row or column fields and choose 'show item with no data' to force display them).

When you add "-1" or other calculation on it, it will try to transform blank value as zero then calculate with numeric value you define.

So I'd like to suggest you add condition on category column to ignore calculate on records which not has value.

Regards,

Xiaoxin Sheng

[AlexisOlson · Accepted solution]

It's subtracting 1 even from all of the rows that return a blank count.

Try something like this instead:

Ahorro=
VAR Cnt = COUNT('(5) MR Header Fields'[srnScnNumber])
RETURN IF(ISBLANK(Cnt), BLANK(), Cnt - 1)

[juan_pablo]

Thank you very much! It works great.

If you have time, can you please explain me a little bit better why this happens and why your solution works?

If not, it's ok.

Thank you.