1

So I have a column like this in Power BI matrix

Country

India
India, Pakistan
Myanmar, Bhutan, India

I want to add a filter, where if I choose India, all the above three entries should return.

Currently my filter looks like this:

India
India, Pakistan
Myanmar, Bhutan, India

I want it to look like this

India
Pakistan
Myanmar
Bhutan
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
SJHA
  • 11
  • 1

2 Answers2

1

You can create a "NEW TABLE" from the modelling menu and use the following DAX query;

FilterTable = 
VAR tmp =
    ADDCOLUMNS ( Sheet1, "ItemPaths", SUBSTITUTE ( CONCATENATEX(Sheet1, Sheet1[Column1]), ",", "|" ) )
RETURN
    DISTINCT(SELECTCOLUMNS (
        GENERATE (
            tmp,
            ADDCOLUMNS (
                GENERATESERIES ( 1, PATHLENGTH ( [ItemPaths] ) ),
                "Items", TRIM(PATHITEM ( [ItemPaths],[Value], TEXT ))
            )
        ),
        "Countries", [Items]
    ))

Replace Sheet1 and Column1 with your table and column name...

Kemal Kaplan
  • 932
  • 8
  • 21
0

Here are the steps which i followed to solve the issue

  1. created a duplicate table as my source table
  2. Deleted all the columns except the one which i wanted to keep a filter for
  3. Duplicated that column and separated it row wise by removing the delimiter
  4. Made a relation b/w my primary table and this newly created filter table (the reln was showing as many to many but go ahead with it)
  5. Apply in the filters, and the same solves the above issue.
SJHA
  • 11
  • 1