CODEWITHSUNDEEP
if-statementconditional-statementspowerbidaxpowerquery
Lorenzo Benassi
Lorenzo Benassi

Reputation: 621

Filter specific value on a concatenate field in DAX

I'm not expert in DAX and Power Query language and I would need help.

I have a table similar to the following:

Item1 Item2 Item3 Item4 Conc_2-4
A     B     C     D     B,C,D
F     G     H     I     G,H,I
L     M     G     C     M,G,C
A     H     D     R     H,D,R

In my report I would like to display a table where there are only the first 4 columns (without the column Conc_2-4), but I would like to be able to filter the table through the column Conc_2-4, without this being displayed in the report.

In other words, I would like to have the distinct values of the items in the filter and when I filter a certain item, the result should return all rows in which this item is present in the columns Item2, Item3, Item4 independently from the position.

EXAMPLE

If in my report I filter the item H, the resulting table in my report should be as follows:

Item1 Item2 Item3 Item4
F     G     H     I
A     H     D     R

Does anyone know how to solve the problem? Maybe using Power Query?

Thanks for any suggestions

Upvotes: 2

Views: 1431

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

Interesting problem. I found one approach that works, but there are probably other solutions as well.

First, we need an index column. Go to the Query Editor and select your Items query. On the Add Column tab, click the Index Column button and then Close & Apply.

Index Column

Now that we have an index column, we will create a new table to act as a slicer. Go to the Modeling tab and create a New Table as follows:

Slicer = UNION(SELECTCOLUMNS(Items, "Item", Items[Item2], "Index", Items[Index]), 
               SELECTCOLUMNS(Items, "Item", Items[Item3], "Index", Items[Index]),
               SELECTCOLUMNS(Items, "Item", Items[Item4], "Index", Items[Index]))

Now under the Relationships screen, create a relationship between the index columns on the two table making sure that the cross-directional filtering applies both ways.

Relationship

That's it! Now you should be able to create a table and slicer that behaves like you specified.

Slicer and Table

Upvotes: 2

Related Questions