Calculation for Related Column in Calculated Column

Hi there,

I have a bit of an odd conundrum. I have a requirement from my users to allow them to choose groupings from a slicer using a pbix file that consists of a single query table and a date dimension. I can’t at this stage do anything that will break bookmarks.

I created a slicer table and then I created an unpivoted table with just the groupings I wanted. I connected it to the main table with a calculated column using the following pseduo-Dax and conected it to my main table with a two way relationship:

Column = SWITCH(
                                   FIRSTNONBLANK('Grouping Choice('Grouping Table'[Group],1),
                                   "Choice 1", RELATED('Other Table'[Choice 1]),
                                   "Choice 2", RELATED('Other Table'[Choice 2),
                     )

So far, so good. When they use it they are able to see the correct data. However, my users are now asking for multi-select. I figured “no problem, I will give them concatenated data.” I created a new measure that concatenates the choices. However, it won’t work in the calculated column, which always returns a single row with blank. Is it even possible to do what I am attempting?

The measure I created was similar to this:

Selected Grouping = VAR GroupString= CALCULATE(CONCATENATEX(VALUES('Group Choice'[Group]), 'Group Choice'[Group], "/ "))

RETURN GroupString

could you get a sample data and explain bit more visual? (tables/relationships/draw the need)
didn’t get exaclty what you need

Its a bit difficult because of NDA, but I’ll see if I can toss something together with a fresh dataset.

Completely Dynamic Ranking Page Example.pbix (3.0 MB)

I’ve included an example file that is similar to my actual working file (less about ten million rows…)

I had originally hoped to write my own blog post about this, but I am frankly stuck on the need to dynamically change the calculation for the dynamic column.

Replying to my own question, but after review, I believe the only way to accomplish this is to add the combinations of the values to the dynamic bridge table and then use them in the SWITCH to choose the related columns (which can be concatenated ).

Hello Diane,
I saw the model, this is a simple solution…
Just put Groulist[Grouping] as the filter insted of ProductionBridge[Group]
this way the filter will nature flow from group to bridge and than to the fact table.

image

Regards

Hi Andrejaar,

Thank you, but that isn’t the intended result. With the relationships, it is easy to have both values pass through. The issue is combining them into a single row, so that the ranking may be based on both of them. That is why the goal is to concatenate them so both related columns may be combined in the result set.

Unfortunately, I think the only way to accomplish this will be to do it in the query editor, by adding more rows to the pivot table to match the intended multiple columns and more rows to the slicer table with the - which isn’t ideal at all.

This is certainly a very unique one, which I haven’t personally come across myself before. I’ll have to do some further thinking around how to potentially solve this one.

Thanks
Sam