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 i...
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

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! 😀