Forum Discussion
Solved
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.
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"
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"