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:

     

cengizhanarslan's avatar
cengizhanarslan
Super User
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 )