CONCATENATEX the Column Header if 0 found in Row

[BrianNeedsHelp]

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	3	0	Device3

I'm trying to Concatenate the Header names (which comes from the DeviceNameShort column) in another column where the row contains a 0. The far right column that I named Concatenate is what I'm trying to achieve.  Please note the Device names are all in one column called DeviceNameShort, but the matrix displays each device name in separate columns.  I did this in a Measure and it lists all the devices needed for the whole report and it is very difficult to read,  instead of keeping the few devices needed in each row.  And it totally elimates the view of the device quantites.  Any help would be awesome.  Thanks! 

DevicesNeeded = IF(
    COUNTROWS(FILTER(IOH, IOH[DAX Product Count] = 0)) > 0,

    CONCATENATEX(IOH, IOH[DeviceModelShort], ", ")
)

 I tried using a calculated column, but I keep getting 'A circular dependency was detected.   There are only two tables in the model.  IOH and Hierarchy.  Hierarchy has the Location and IOH has the DAX Product Count measure and DeviceModelShort column. 

[v-dineshya]

Hi @BrianNeedsHelp ,

Thank you for reaching out to the Microsoft Community Forum.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided. If possible please provide sample PBIX file, it will help us to replicate the scenario.

Regards,

Dinesh

[MohamedFowzan1 · Accepted solution]

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]

@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?  

[FBergamaschi]

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here