SOLVED

Dax Measure - If Null add text

%3CLINGO-SUB%20id%3D%22lingo-sub-3121645%22%20slang%3D%22en-US%22%3EDax%20Measure%20-%20If%20Null%20add%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3121645%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%2C%20I%20have%20a%20power%20pivot%20as%20shown%20below%20with%20a%20dax%20measure.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20display%20text%20when%20the%20date%20(Settled%20Date)%20is%20blank.%3C%2FP%3E%3CP%3EHow%20can%20I%20do%20this%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Tony2021_0-1644106852189.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F345594iF0258812C1CBA11A%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Tony2021_0-1644106852189.png%22%20alt%3D%22Tony2021_0-1644106852189.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3121645%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3121719%22%20slang%3D%22en-US%22%3ERe%3A%20Dax%20Measure%20-%20If%20Null%20add%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3121719%22%20slang%3D%22en-US%22%3EI'm%20not%20clear%20on%20how%20you%20create%20that%20field%20but%20can't%20you%20just%20put%20the%20text%20in%20to%20the%20formula%3F%3CBR%20%2F%3EAlternatively%20you%20can%20use%20a%20custom%20number%20format%20so%20that%20Text%20or%20Zero%20values%20(depending%20on%20what%20that%20%22blanks%22%20really%20are)%20displays%20the%20text.%20The%20advantage%20of%20this%20approach%20is%20that%20when%20you%20do%20calculation%20on%20this%20column%20the%20VALUE%20is%20still%20%22blank%22%20(0%20or%20%22%22)%20instead%20of%20that%20text%20but%20the%20disadvantage%20is%20just%20the%20opposite%20(i.e.%20you%20can't%20do%20a%20search%20or%20conditional%20or%20filter%20expecting%20to%20find%20that%20%22text%22%20that%20is%20being%20displayed%20since%20it%20is%20there%20only%20in%20appearance.%20To%20do%20the%20custom%20number%20formatting%20go%20to%20Home%20-%20Number%20-%20More%20Number%20Formats.%20Select%20Custom.%20and%20then%20enter%20in%20the%20%22Type%22%20field%20something%20like%3A%3CBR%20%2F%3Em%2Fd%2Fyyyy%3B%20%22illegal%20neg%20number%22%3B%20%22zero%22%3B%20%22empty%20text%22%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3122066%22%20slang%3D%22en-US%22%3ERe%3A%20Dax%20Measure%20-%20If%20Null%20add%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3122066%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029845%22%20target%3D%22_blank%22%3E%40Tony2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20to%20modify%20this%20measure%20or%20use%20another%20one%20on%20the%20top%20of%20it%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3EDate%20Settled%3A%3DIF(%20ISBLANK(%5BSettled%20Date%5D)%2C%20%22Not%20Settled%22%2C%20%5BSettled%20Date%5D%20)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3122801%22%20slang%3D%22en-US%22%3ERe%3A%20Dax%20Measure%20-%20If%20Null%20add%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3122801%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Sergei%2C%3CBR%20%2F%3Ethank%20you%20for%20the%20response.%3CBR%20%2F%3EI%20seem%20to%20have%20done%20something%20wrong.%3C%2FP%3E%3CP%3EI%20add%20a%20measure%20and%20pasted%20the%20formula%20(I%20did%20slightly%20modify%20to%20add%20Table%202%20in%20front%20of%20%5BSettled%20Date%5D%20as%20you%20can%20see%20in%20the%20pic%20below.%26nbsp%3B%20I%20guess%20that%20is%20correct.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20get%20an%20error%20saying%20that%20%5BSettled%20Date%5D%20cannot%20be%20determined.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20do%20you%20suggest%3F%26nbsp%3B%20thank%20you.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Tony2021_0-1644164482418.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F345673iDC1D0CA043C3905A%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Tony2021_0-1644164482418.png%22%20alt%3D%22Tony2021_0-1644164482418.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Super Contributor

 

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

Tony2021_0-1644106852189.png

 

8 Replies
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"

@Tony2021 

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] )

@Sergei Baklan 

 

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.  

 

Tony2021_0-1644164482418.png

 

@Tony2021 

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.

@Sergei Baklan 

Sergei, I think it is only a measure and not calculated. I do not do any calculations on the Settled Date...only a measure. But maybe I am missing something. 

here is a screen shot of the measures I have:

I have 4 measures.  I dont think any are considered calculated. 

Let me know what is the next step.  thank you

Tony2021_0-1644168997902.png

 

best response confirmed by Tony2021 (Super Contributor)
Solution

@Tony2021 

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.

Amazing. That worked. I used MIN as suggested and the IsBlank. thank you very much for the help. Works perfectly.

@Tony2021 , you are welcome, glad to help