Assign "Data Not available" for blank values for below dax

[Anonymous]

I've a few blank rows in the Occupation column of Branch office table and I want to assign "Data Not available" when I extract occupation column values to my HEAD_OFFICE table. How can I update the below dax for the same?

=CALCULATE(FIRSTNONBLANK(BRANCH_OFFICE[Name],1),

filter(all(BRANCH_OFFICE), BRANCH_OFFICE[Occupation] ="Salesman" &&

HEAD_OFFICE[Office_ID]= BRANCH_OFFICE[Office_ID]))

@amitchandak  @Greg_Deckler , @Fowmy 

[BA_Pete · Accepted solution]

Hi @Anonymous ,

Try this please:

newMeasure =
VAR yourCalc =
CALCULATE(
    FIRSTNONBLANK(BRANCH_OFFICE[Name], 1),
    FILTER(
        ALL(BRANCH_OFFICE),
        BRANCH_OFFICE[Occupation] = "Salesman"
        && HEAD_OFFICE[Office_ID] = BRANCH_OFFICE[Office_ID]
    )
)
RETURN
IF(ISBLANK(yourCalc), "Data Not Available", yourCalc)

Pete

[Anonymous]

Hey @BA_Pete  this is not working for Date column, any idea why or should I make any change in dax? 

[BA_Pete]

Hi @Anonymous ,

I would say that this is because you are trying to assign a text-type output to a date-type field/measure.

One possible solution would be to duplicate your date field in power query and then change the data type to text. You can then use the original date field for time intelligence and relationships, but use the text version for measures/visuals such as in this case.

Pete

[Anonymous]

Thanks @BA_Pete , this works perfectly!!