Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

ISblank() is not working as expected

Hello All,

 

In below screen column Plant is coming from different table and "LocationText" is coming from a different table and I have joined these two columns in modeling tab. and whenever there is no match it is displaying Blank().

 

 

I have created  a measure "% Claims >3 days Target" and I need to display "N/A" when it is blank and my measure is a follows:

 
Measure  :
 
% Claims > 3 Days Target 70% (Workers Compensation) = if(max('Reporting Lag'[LOCATIONTEXT])=BLANK(),"N/A",
sum('Reporting Lag'[PctToTotalClaims]))
 
It is not displaying ""N/A" when there is blank.
 

 

 

 

 

 

 

 

Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi Anonymous ,

     

    Are you sure that the text fields are blanks? Can it be that there are some spaces?

     

    Try to make the following code:

    % Claims > 3 Days Target 70% (Workers Compensation) =
IF (
    MAX ( 'Reporting Lag'[LOCATIONTEXT] ) = "",
    "N/A",
    SUM ( 'Reporting Lag'[PctToTotalClaims] )
)

    If it keeps doing not returning correct value use this measure to see if the blanks are really blanks:

    Length = LEN(MAX('Reporting Lag'[LOCATIONTEXT] ))

    If the values is greate then 0 or null then it's because the field is not blank.

     

    Regards,

    MFelix

     

2 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    7 years ago

    Hi Anonymous ,

     

    Are you sure that the text fields are blanks? Can it be that there are some spaces?

     

    Try to make the following code:

    % Claims > 3 Days Target 70% (Workers Compensation) =
IF (
    MAX ( 'Reporting Lag'[LOCATIONTEXT] ) = "",
    "N/A",
    SUM ( 'Reporting Lag'[PctToTotalClaims] )
)

    If it keeps doing not returning correct value use this measure to see if the blanks are really blanks:

    Length = LEN(MAX('Reporting Lag'[LOCATIONTEXT] ))

    If the values is greate then 0 or null then it's because the field is not blank.

     

    Regards,

    MFelix

     

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous 

    MFelix's suggestion would be useful, you could check on your side.

     

    I wonder if "Plant" and "LOCATIONTEXT" are in the same table

    or in the two tables but you add two columns in a table visual?

     

     

    Best Regards

    Maggie Community Support Team _ Maggie Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.