Forum Discussion

spandy34's avatar
spandy34
Icon for Responsive Resident rankResponsive Resident
3 years ago
Solved

DAX - Blank Data

I wondered if anyone could help me please.   I want the calculation to take subtract the Current Permance from Previous Performance but if the Previous performance is blank then i want to field to ...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    Hi spandy34

     

    For a calculated column, the values returned should be in the same data type so your expected result cannot be achieved. Currently "[Current Performance]-[Previous Performance]" will return a number while " No Previous Data" is in Text type. Returning different types of values in a calculated column will give you the following variant-type error. 

     

    Please consider a different result when the Previous performance is blank. Or you can always convert "[Current Performance]-[Previous Performance]" result to Text type with CONVERT function. 

    Difference =
IF (
    ISBLANK ( [Previous Performance] ),
    "No Previous Data",
    CONVERT ( [Current Performance] - [Previous Performance], STRING )
)

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

DOLEARY85's avatar
DOLEARY85
Icon for Resident Rockstar rankResident Rockstar
3 years ago

Can you share an example of what your data looks like, as it looks like the measure you referenced is made up of 2 other measures.

spandy34's avatar
spandy34
Icon for Responsive Resident rankResponsive Resident
3 years ago

 

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi spandy34

     

    For a calculated column, the values returned should be in the same data type so your expected result cannot be achieved. Currently "[Current Performance]-[Previous Performance]" will return a number while " No Previous Data" is in Text type. Returning different types of values in a calculated column will give you the following variant-type error. 

     

    Please consider a different result when the Previous performance is blank. Or you can always convert "[Current Performance]-[Previous Performance]" result to Text type with CONVERT function. 

    Difference =
IF (
    ISBLANK ( [Previous Performance] ),
    "No Previous Data",
    CONVERT ( [Current Performance] - [Previous Performance], STRING )
)

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.