Concatenate the values of a dimenstion field that is connected to (dim[field] -> brg <-> dim -> fct

[Tyler_Rinker]

My data is about workers and their clock in and out times at different buildings.  These building have multiple descriptive tags associated with them.  I am attempting to create a table visual that uses fields from a fact (fct_check_in) and dimension tables.  It all goes well until I want to include a concatenated field (building_tags) from a dimension table (dim_building_tag) that is connected to another dimension table (dim_building) via a bridge table (brg_building_to_building_tags) used to address a many to many relationship.  

I am using a measure to try to make this column in the table visual so that the results can be dynamic (only some tags in the tag slicer might be selected for example).

I tried:

concatenated_building_tags =
CONCATENATEX(
    VAR CurrentBuildingID = SELECTEDVALUE(fct_check_in[building_id])
    RETURN
        FILTER(
            brg_building_to_building_tag,
            brg_building_to_building_tag[building_id] = CurrentBuildingID
        ),
    RELATED(dim_building_tag[building_tag]),
    "; "
)

but this results in no rows being returned.  I also tried:

concatenated_building_tags_3 =
CONCATENATEX(
    VALUES(dim_building_tag[building_tag]),
    dim_building_tag[building_tag],
    "; "
)

But that results in extra rows being returned with all tags on every row. 

How can I concatenate just the tags associated with that building for each row in alphabetical order as seen in the following image?

 MWE pbix concatenatex_fct_dim_brg_dim.pbix 

[jgeddes]

Here is an alternative measure that may also work for you.

Measure = 
var _selectedBuilding = 
SELECTEDVALUE(fct_check_in[building_id])
var _filteredTable =
SELECTCOLUMNS(
		FILTER(
			brg_building_to_building_tag,
			brg_building_to_building_tag[building_id] = _selectedBuilding
		),
		"__value", RELATED(dim_building_tag[building_tag])
	)
var _result = 
CONCATENATEX(
    _filteredTable,
    [__value],
    "; ",
    [__value],
    ASC
)
Return
_result

[Tyler_Rinker]

@jgeddes I tried your approach and pasted in exactly as you had it but it results in 0 rows returned in the table visual.  I suspect you tweaked the model too? Your output image looks like what I'm after.  

[jgeddes · Accepted solution]

I did change one of the relationships as the building_id (number) was related to the building name. 

Here is the pbix.

[Tyler_Rinker]

Ah silly connection error on my part.  Works well!  Thank you!

[rajendraongole1]

Hi @Tyler_Rinker -concatenated tags are sorted alphabetically in Power BI, modify your DAX formula to explicitly include the sorting order in CONCATENATEX. 

use below logic

concatenated_building_tags_sort =

CONCATENATEX(

    VALUES(dim_building_tag[building_tag]),

    dim_building_tag[building_tag],

    "; ",

    dim_building_tag[building_tag],

    ASC

)

Attached pbix file FYR. Hope it helps.

[Tyler_Rinker]

@rajendraongole1 that tackles the sorting part but the harder part is making sure the right building tags go with the right bulding.  Each building has at most 3 tags but the current DAX will make 4 tags (all tags are shown).  If you see my last image it shows the tags I'd expect per row with All selected in the available slicers for buildings and uilding tags.  Additionally this current measure causes additional rows without buildings to be created (which can be fixed with a table filter but usually extra rows means my DAX is not ideal).

Preview