Help Concatenatex with Blanks Giving Unexpected Results (Sample Included)

[rossnruthie]

Link to sample report pbix: Concatenate With Blanks

I have a report where I am trying to use the Concatenatex function to create a list of features associated with a vehicles catalogue.  My model looks like this:

As you can see for a given OEM->Vehicle->Catalogue there can be many features associated with it.  My initial goal was to display all features as a concatenated list of values in a single row rather than duplicating all rows per feature.  So I created a measure:

FeatureList = 
			CONCATENATEX(
				VALUES('Features'[FeatureDescription]),
				'Features'[FeatureDescription],
				", ",
				'Features'[FeatureDescription],
				ASC
			)

While this does concatenate all values into a single row, it has the unintended side effect of hiding any catalogue that doesn't have any features. 

So I created a second measure using the original FeatureList as a base.

FeatureListWithBlanks = IF(ISBLANK([FeatureList]),"N/A",[FeatureList])

And here is where it comes of the rails.  The result of this measure appears to be the cartesian product of all 4 tables with a bunch of repeating values.  So is there a way in DAX i can do this properly so that I display the brand, vehicle, catalogue and it's concatenated list of values even if blank?

I've also tried:

1. Right click a value in visual and select show items with no data
   1. My actual model is massive and doing this blows out the memory.
2. Add a dummy measure Count('Catalogue'[CatalogueKey]) as a column and then "hide" the column.  This displays all the blanks and you can't see the dummy column since i collapse it but when the user exports the data this column is included and is not an ideal solution.

Result of FeatureListWithBlanks:

[parry2k]

@rossnruthie add it as a column

FeatureListAsCol = CALCULATE(
			CONCATENATEX(
					VALUES('Features'[FeatureDescription]),
				'Features'[FeatureDescription],
				", ",
				'Features'[FeatureDescription],
				ASC
			)
)

[rossnruthie · Accepted solution]

@parry2k thanks for the suggestion.

Unfortunately in my situation there are quite a few of these fields and due to the size of the underlying model we would prefer to keep them as measures rather than storing all of them in memory as a calculated column.

The good news though is that I've modified my measure so that it works and doesn't blow out the memory.  I basically nest my original measure inside an additional IF() that checks for the existence of a record in the Catalogue table.  If a catalogue record exists then I want to display something (either "N/A" or the features if they exist) and if a catalogue record doesn't exist then keep it BLANK.

I've changed the calculation to:

FeatureListWithBlanks = 
	IF(
            ISBLANK(MAX(Catalogue[CatalogueKey])),
            BLANK(),
            IF(
               ISBLANK([FeatureList]),
               "N/A",
               [FeatureList]
               )
           )

And this displays as I would expect.

 

[parry2k]

@rossnruthie glad, it worked. Infact I do prefer  measure but was not sure about the model. It is good that you have working solution. Cheers