Forum Discussion

BrianNeedsHelp's avatar
BrianNeedsHelp
Icon for Resolver I rankResolver I
1 year ago
Solved

CONCATENATEX the Column Header if 0 found in Row

I have a matrix table like this:   Location     Device1     Device2     Device3     Concatenate A 1 0 0 Device2, Device3 B 0 4 5 Device1 C 0 0 3 Device1,Device2 D 1 ...
CONCATENATEX
  • MohamedFowzan1's avatar
    MohamedFowzan1
    1 year ago

    Hi BrianNeedsHelp 

     

    Could you try this, I recreated your scenario and it seemed to work:

    DevicesNeeded = 
VAR DeviceList = {
    IF(IOH[Device1] = 0, "Device1", BLANK()),
    IF(IOH[Device2] = 0, "Device2", BLANK()),
    IF(IOH[Device3] = 0, "Device3", BLANK())
}
VAR FilteredDevices = FILTER(DeviceList, NOT(ISBLANK([Value])))
RETURN
CONCATENATEX(FilteredDevices, [Value], ", ")

     
    In the case where data is not pivotted, create measure:

    DevicesNeeded2 = 
CONCATENATEX(
    FILTER(
        IOH,
        IOH[Location] = SELECTEDVALUE(IOH[Location]) &&
        IOH[Val] = 0
    ),
    IOH[DeviceModelShort],
    ", "
)

     

MohamedFowzan1's avatar
MohamedFowzan1
Icon for Super User rankSuper User
1 year ago

Hi BrianNeedsHelp 

 

Could you try this, I recreated your scenario and it seemed to work:

DevicesNeeded = 
VAR DeviceList = {
    IF(IOH[Device1] = 0, "Device1", BLANK()),
    IF(IOH[Device2] = 0, "Device2", BLANK()),
    IF(IOH[Device3] = 0, "Device3", BLANK())
}
VAR FilteredDevices = FILTER(DeviceList, NOT(ISBLANK([Value])))
RETURN
CONCATENATEX(FilteredDevices, [Value], ", ")

 
In the case where data is not pivotted, create measure:

DevicesNeeded2 = 
CONCATENATEX(
    FILTER(
        IOH,
        IOH[Location] = SELECTEDVALUE(IOH[Location]) &&
        IOH[Val] = 0
    ),
    IOH[DeviceModelShort],
    ", "
)

 

BrianNeedsHelp's avatar
BrianNeedsHelp
Icon for Resolver I rankResolver I
1 year ago

MohamedFowzan1 My model looks like this:  

DeviceModelShort is in IOH and is one column, so there is not [Device1], [Device2] etc. as separate columns. [DaxProductCount] is the measure that shows quantities of each device such as 1 or 0.   I have it in a matrix so the devices show in different columns, but it comes from one column DeviceModelShort.  Could you please try and recreate like described and in the images?