Forum Discussion

AsmaAlNashash's avatar
AsmaAlNashash
Frequent Visitor
3 years ago
Solved

Switch Case based on NULL values

Hi, I am currently trying to do something very simple. I want to create a new column in which if only the first column is Not Null, the value will be 1. If the First and Second columns are not Null then the value will be 2, and so on. The columns are from different tables.

I was not able to Use ISBLANK() with a SWITCH Function.

Here is an example to make it clear.

 

 

Anyone have any ideas would be appreciated.

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Asma,

    I have created dummy data for your reference, hopefully your requirement is fulfilled.

    Note: You can use LOOKUPVALUE Dax in order to get columns from different tables.

    Dax - 

    Column =
    Var a= if(ISBLANK('Table (2)'[Column1]),0,1)
    var b = if(ISBLANK('Table (2)'[Column2]),0,1)
    var c = if(ISBLANK('Table (2)'[Column3]),0,1)
    var d = if(ISBLANK('Table (2)'[Column4]),0,1)
    Var e = a+b+c+d
    return e


    Please refer to the below image.

     

2 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    3 years ago

    Hi AsmaAlNashash ,

     

    You could try like this:-

     

    StageID =
IF ( ISBLANK ( MAX ( 'Table (1)'[Column1] ) ), 0, 1 )
    + IF ( ISBLANK ( MAX ( 'Table (2)'[Column2] ) ), 0, 1 )
    + IF ( ISBLANK ( MAX ( 'Table (3)'[Column3] ) ), 0, 1 )
    + IF ( ISBLANK ( MAX ( 'Table (4)'[Column4] ) ), 0, 1 )

     

     

    Thanks,

    Samarth

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Asma,

    I have created dummy data for your reference, hopefully your requirement is fulfilled.

    Note: You can use LOOKUPVALUE Dax in order to get columns from different tables.

    Dax - 

    Column =
    Var a= if(ISBLANK('Table (2)'[Column1]),0,1)
    var b = if(ISBLANK('Table (2)'[Column2]),0,1)
    var c = if(ISBLANK('Table (2)'[Column3]),0,1)
    var d = if(ISBLANK('Table (2)'[Column4]),0,1)
    Var e = a+b+c+d
    return e


    Please refer to the below image.