Forum Discussion

o-johnralphp's avatar
o-johnralphp
Advocate I
7 months ago
Solved

Return Column Name if Zero Value

Dear All,   Need your expertise to create a calculated column to return the column name if the value is zero for an item. Would also help if the dax can be used as a part of Switch function since ...
Return Column Name if Zero Value
  • cengizhanarslan's avatar
    cengizhanarslan
    7 months ago

    Please try the formula below:

    Zero Stores =
VAR Result =
    CONCATENATEX (
        {
            IF ( 'Table'[Store1] = 0, "Store1", BLANK () ),
            IF ( 'Table'[Store2] = 0, "Store2", BLANK () ),
            IF ( 'Table'[Store3] = 0, "Store3", BLANK () ),
            IF ( 'Table'[Store4] = 0, "Store4", BLANK () ),
            IF ( 'Table'[Store5] = 0, "Store5", BLANK () )
        },
        [Value],
        ","
    )
RETURN
IF ( Result = "", BLANK (), Result )
  • FreemanZ's avatar
    FreemanZ
    7 months ago

    hi o-johnralphp ，

     

    This is achievable by tweaking cengizhanarslan 's code like this:

    Zero Stores = 
VAR Result =
    CONCATENATEX (
        FILTER(
            {
                IF ( 'Table'[Store1] = 0, "Store1", BLANK () ),
                IF ( 'Table'[Store2] = 0, "Store2", BLANK () ),
                IF ( 'Table'[Store3] = 0, "Store3", BLANK () ),
                IF ( 'Table'[Store4] = 0, "Store4", BLANK () ),
                IF ( 'Table'[Store5] = 0, "Store5", BLANK () )
            },
            NOT ISBLANK([Value])
        ),
        [Value],
        ","
    )
RETURN
IF ( Result = "", BLANK (), Result )

     

    it worked like:

     

FreemanZ's avatar
FreemanZ
Super User
7 months ago

hi o-johnralphp ,

 

it is also advisible to unpivot your data to something like:

 

item-store-value

Apple-Store1-5

Apple-Store2-0

...

 

Then you can easily identify which item has zero value. This is helpful especially if you have large tables.