Forum Discussion

spandy34's avatar
spandy34
Responsive 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
Resident Rockstar
3 years ago

Hi,

 

you could use: if(isblank([Difference in Performance]),"No Previous Data",[Difference in Performance])

 

or 

 

COALESCE([Difference in Performance],"No Previous Data")

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

spandy34's avatar
spandy34
Responsive Resident
3 years ago

Sorry , I want to create a calculated column.  Please can you amend the DAX for a column.  Sorry

  • DOLEARY85's avatar
    DOLEARY85
    Resident 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
      Responsive Resident
      3 years ago

       

      • v-jingzhang's avatar
        v-jingzhang
        Community 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.