Forum Discussion

o-johnralphp's avatar
o-johnralphp
Icon for Advocate I rankAdvocate 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:

     

Kedar_Pande's avatar
Kedar_Pande
Icon for Super User rankSuper User
7 months ago

o-johnralphp 

 

Calculated column:

Zero Stores = 
VAR Store1Zero = IF([Store1] = 0, "Store1", "")
VAR Store2Zero = IF([Store2] = 0, "Store2", "")
VAR Store3Zero = IF([Store3] = 0, "Store3", "")
VAR Store4Zero = IF([Store4] = 0, "Store4", "")
VAR Store5Zero = IF([Store5] = 0, "Store5", "")
RETURN
CONCATENATEX(
{Store1Zero, Store2Zero, Store3Zero, Store4Zero, Store5Zero},
[Value],
",",
[Value] <> ""
)

 

Use in SWITCH:

Result = SWITCH(
TRUE(),
[Zero Stores] = "Store2,Store4", "Case1",
[Zero Stores] = "Store1,Store5", "Case2",
"Default"
)

 

If this answer helped, please click 👍 or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

  • o-johnralphp's avatar
    o-johnralphp
    Icon for Advocate I rankAdvocate I
    7 months ago

    Kedar_Pande . Thanks for providing an alternative DAX, would like also to point out that these will also return the extra delimeters as I've mentioned earlier.