Forum Discussion
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 this would only be a part of a whole logic that im working on.
Kindly refer to the table:
| Item | Store1 | Store2 | Store3 | Store4 | Store5 | Desired 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 |
Many thanks in advance!!!
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:
8 Replies
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 ,
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.
FreemanZ thank you for the input
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-pandeKedar_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.
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.
