CODEWITHSUNDEEP
powerbipowerbi-desktoppowerbi-custom-visualspowerbi-filters
SJHA
SJHA

Reputation: 11

Comma delimiter filter in Power BI

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

Upvotes: 1

Views: 848

Answers (2)

SJHA
SJHA

Reputation: 11

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.

Upvotes: 0

Kemal Kaplan
Kemal Kaplan

Reputation: 1024

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...

Upvotes: 1

Related Questions