Forum Discussion
Dax Measure - If Null add text
Hello, I have a power pivot as shown below with a dax measure.
I want to display text when the date (Settled Date) is blank.
How can I do this?
thank you
I see, so Settled Date is the column in Table2, not measure. I have no idea which aggregation you'd like to show for this field in PivotTable, let assume min of Settled Date:
min Settled:=MINX( Table1, Table1[Settled Date] )In addition, when above is blank we would like to show some text. That could be
Date Settled:=IF( ISBLANK([min Settled]), "Not Settled", [min Settled] )Of course, both could be combined in one measure, depends on goals.
8 Replies
As variant to modify this measure or use another one on the top of it
Date Settled:=IF( ISBLANK([Settled Date]), "Not Settled", [Settled Date] )Hi Sergei,
thank you for the response.
I seem to have done something wrong.I add a measure and pasted the formula (I did slightly modify to add Table 2 in front of [Settled Date] as you can see in the pic below. I guess that is correct.
I get an error saying that [Settled Date] cannot be determined.
What do you suggest? thank you.
Based on your screenshot my understanding was that Settled Date is measure, not calculated column. If so, please create another measure exactly as in my sample.
If that is /calculated/ column as in your latest post - that doesn't work, it shall be something else depends on which and where result you'd like to have. We need to know what measure shall return for non-blanks.
- I'm not clear on how you create that field but can't you just put the text in to the formula?
Alternatively you can use a custom number format so that Text or Zero values (depending on what that "blanks" really are) displays the text. The advantage of this approach is that when you do calculation on this column the VALUE is still "blank" (0 or "") instead of that text but the disadvantage is just the opposite (i.e. you can't do a search or conditional or filter expecting to find that "text" that is being displayed since it is there only in appearance. To do the custom number formatting go to Home - Number - More Number Formats. Select Custom. and then enter in the "Type" field something like:
m/d/yyyy; "illegal neg number"; "zero"; "empty text"
