Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calulate difference between columns if has numbers. Otherwise give tekst with explanation

Hi all,

 

I have a table with 2 columns with numbers an some nulls. I would like to create a measure in DAX wich gives me the differens (column 2019-Column 2018) if both columns have numbers. And if column 2019 has a null I want to return "There is no value for 2019" and the same for column 2018. See below a short example of the dataset

 

20182019Desired column
null100has no value for 2018
200100-100
50500
100nullhas no value for 2019
   

 

Is this solvable with a measure in DAX or would I need to actually create the columns in DAX/M?

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    HI @BobKoenen,

    The lkalawski formula looks good, but the current computed column and measure are not able to directly return multiple data types. I think you need to add some additional operations in the formula to convert them to text to avoid the data type problem. (I used the if statement to check the field values)

    Column Version =
IF (
    [2018] = BLANK (),
    "2018 missed",
    IF ( [2019] = BLANK (), "2019 missed", [2019] - [2018] )
) & ""

Measure version = 
CONCATENATEX (
    SUMMARIZE (
        'Table',
        [2018],
        [2019],
        "Result",
            IF (
                [2018] = BLANK (),
                "2018 missed",
                IF ( [2019] = BLANK (), "2019 missed", [2019] - [2018] )
            ) & ""
    ),
    [Result],
    ","
)

    Best regards

    Xiaoxin Sheng

2 Replies

  • lkalawski's avatar
    lkalawski
    Resident Rockstar
    5 years ago

    Hi Anonymous 

    You can create measure:

    Desired column M = 
VAR _2018 = SELECTEDVALUE('Table (2)'[2018])
VAR _2019 = SELECTEDVALUE('Table (2)'[2019])

RETURN
SWITCH(TRUE(),
ISBLANK(_2018), "has no value for 2018",
ISBLANK(_2019), "has no value for 2019",
_2018 - _2019
)

    Or, you can also create a calculated column:

    Desired column CC = 
SWITCH(TRUE(),
ISBLANK('Table (2)'[2018]), "has no value for 2018",
ISBLANK('Table (2)'[2019]), "has no value for 2019",
CONVERT('Table (2)'[2018] - 'Table (2)'[2019], STRING)
)

     

    Please check this article to choose the proper one:

    https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/



    _______________
    If I helped, please accept the solution and give kudos! 😀

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HI @BobKoenen,

    The lkalawski formula looks good, but the current computed column and measure are not able to directly return multiple data types. I think you need to add some additional operations in the formula to convert them to text to avoid the data type problem. (I used the if statement to check the field values)

    Column Version =
IF (
    [2018] = BLANK (),
    "2018 missed",
    IF ( [2019] = BLANK (), "2019 missed", [2019] - [2018] )
) & ""

Measure version = 
CONCATENATEX (
    SUMMARIZE (
        'Table',
        [2018],
        [2019],
        "Result",
            IF (
                [2018] = BLANK (),
                "2018 missed",
                IF ( [2019] = BLANK (), "2019 missed", [2019] - [2018] )
            ) & ""
    ),
    [Result],
    ","
)

    Best regards

    Xiaoxin Sheng