Forum Discussion
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 ...
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 )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:
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?
| Item | Store1 | Store2 | Store3 | Store4 | Store5 | Actual Output |
| Apple | 5 | 0 | 5.2 | 0 | 4.8 | ,Store2,,Store4 |
| Orange | 0 | 3.8 | 4 | 4 | 0 | Store1,,,Store5 |
| Grapes | 6.5 | 6 | 0 | 0 | 0 | ,,Store3,Store4,Store5 |
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:
Thank you very much! Much appreciated.