DAX measure using 'if' returns zero rather than false = " "

Hi all,

I have a measure which uses and if function, but even though I've eded with fasle = " " when dropped into a pivot table
I get 0's where the for in this case "b" and "c".

Does anyone know why and also I've been trying to write it as a measure within a measure to save typing a everything twice

so I could just use If( measure = true , measure = false , " " )

I've attached workbook and notepad with examples of what I've trued so far.

Richard.

Sorry I though that would make things clearer, obviously not, if the measure is true then use the measure if it is not true return a blank.
Just wondered how to avoid having to type the entire formula out twice which is what I've done in the attachment.

Richard.

Sorry , I'm confusing myself, essentially I have a measure which runs ;

< if ( VAR mdate = MAX(Table1[Date]) RETURN CALCULATE(SUM(Table1[Units]),

KEEPFILTERS(Table1[Product]="a"),

FILTER(ALL(Table1[Date]),Table1[Date]<=mdate && Table1[Date]> EOMONTH(mdate,-1)) ),

(then) VAR mdate = MAX(Table1[Date]) RETURN CALCULATE(SUM(Table1[Units]),

KEEPFILTERS(Table1[Product]="a"),

FILTER(ALL(Table1[Date]),Table1[Date]<=mdate && Table1[Date]> EOMONTH(mdate,-1)) )

(otherwise) , " " ))

This return what I wanted all in a pivot table all the product column remains but I only have running total for "a"
and I wondered if it would be possible somehow to declare it once as a Variable and then use something like ;



If ( Variable , Variable , " " )

Rory gave me the idea in reply earlier question.

Richard.

Actually I've checked and it was Bo_Ry he suggested making a measure using a variable;

So ; measure:= VAR V-name = sum(Table [column ] ) RETURN if ( V-name , V-name, " " )

and I'd like to do the same that is create a variable for the above rather than typing everything twice, but as it also contains a variable I'm having difficulties. Oh, and yes I'd also like to find out why it reuturns zero's rather than just a blank, ever when " " is used at the end?

Richard.

The expected result is the same as the result in the attached workbook, I just don't want to have to type everything twice,
so I thought if it is possible to declare it as a variable and then use that . but as the formua contains a variable in itself I don't seem to be able to get it right.
Essentially you suggested I use the following for a similar situation ;

< measure VAR name = SUM( Table[Column] ) RETURN
IF ( name, name, " " ) >


Also if you have an explanation as to why when dropped in a pivot table it return zero's rather then just a blank I'd be interested to know.

Richard.

Hi,
I know this goes back some time, but I managed to get what I wanted, and have attached a small work book with two measures,
one with IF and one without, showing what I meant, essentially applying an internal filter but still keep the entire pivot table when it's dropped in.

Richard.

Hello,
I know I raised this some time ago, but have only just discovered that rather than
declaring the formula as a variable and then using

= if ( VAR, VAR, "" )

, to keep rows without data, you can just
go to options, display, show items with no data, there's also a similar option in power bi, on the Column and Row drop down menus.
But you probably knew that already. 😉
RD