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:

     

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

Thank You!

 

Just a follow up question how to prevent the extra delimiters? commas are added before the first entry this happens when the preceeding column is non a zero?

 

ItemStore1Store2Store3Store4Store5Actual Output
Apple505.204.8,Store2,,Store4
Orange03.8440Store1,,,Store5
Grapes6.56000,,Store3,Store4,Store5

 

 

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    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:

     

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

      Thank you very much! Much appreciated.