List of values with filter, kindly help me!

[Ayyappa5678]

Hi Team,

Kindly help me for filter visual.

I have the 'Data' column and categories columns. like below snapshot format. 

we required filter, If I select categories col of 'a' then "a" related all data shown like below snapshot.
If select 'b' then "b" related of all data required.

How to it in DAX with this?
Note:- In power query it is possible, by splitting muliple cols and we will do it. but have performacne impact, kindly help me to it using DAX?

[Anonymous · Accepted solution]

Thanks for the replies from FarhanJeelani.

Hi @Ayyappa5678 ,

You can follow the steps below:

1. Create a calculated table:

Categories = 
DISTINCT(
    SELECTCOLUMNS(
        FILTER(
            ADDCOLUMNS(
                GENERATESERIES(1, LEN(CONCATENATEX('Table', 'Table'[categories], ","))),
                "Letter", MID(CONCATENATEX('Table', 'Table'[categories], ","), [Value], 1)
            ),
            MID(CONCATENATEX('Table', 'Table'[categories], ","), [Value], 1) <> ","
        ),
        "Category", [Letter]
    )
)

2. Create a measure:

measure = 
VAR _select =
    SELECTEDVALUE ( 'Categories'[Category] )
RETURN
    IF ( CONTAINSSTRING ( MAX ( 'Table'[categories] ), _select ), 1, 0 )

3. Filter out data where the measure equals 1:

Result:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[Ayyappa5678]

@FarhanJeelani  - While doing STEP 1 getting this error. Kindly help me.

[FarhanJeelani]

try :

Category_Table =
VAR AllCategories = CONCATENATEX(YourTable, YourTable[categories], ",")
VAR CategoryList =
ADDCOLUMNS(
GENERATE(
VALUES('YourTable'[categories]),
SELECTCOLUMNS(
GENERATESERIES(1, 10), -- Adjust this if you expect more than 10 items
"Position", [Value]
)
),
"Category", PATHITEM('YourTable'[categories], [Position], ",")
)
RETURN
DISTINCT(SELECTCOLUMNS(CategoryList, "Category", [Category]))

[FarhanJeelani]

Hi @Ayyappa5678 ,

To achieve your goal of filtering the Data column based on the selected category without splitting columns in Power Query (which can cause performance issues), we can use a DAX measure and a slicer with a calculated table.

Step 1: Create a Calculated Table for Categories
Generate a table with unique category values extracted from the categories column.

Category_Table =
VAR AllCategories = CONCATENATEX(YourTable, YourTable[categories], ",")
VAR CategoryList =
ADDCOLUMNS(
GENERATE(
VALUES('YourTable'[categories]),
SELECTCOLUMNS(
GENERATESERIES(1, 10), -- Adjust this if you expect more than 10 items
"Position", [Value]
)
),
"Category", PATHITEM('YourTable'[categories], [Position], ",")
)
RETURN
DISTINCT(SELECTCOLUMNS(CategoryList, "Category", [Category]))
​

Step 2: Create a Filtered Measure
We’ll create a measure to filter the Data based on the selected category.

FilteredData = 
VAR SelectedCategory = SELECTEDVALUE('Category_Table'[Category])
RETURN
IF(
    NOT ISBLANK(SelectedCategory),
    IF(
        CONTAINSSTRING(YourTable[categories], SelectedCategory),
        1,
        0
    ),
    1
)

Step 3: Add Visuals
Add a slicer visual with Category_Table[Category].
Add a table visual with YourTable[Data] and apply a filter where FilteredData = 1.

Please mark this post as solution if it helps you. Appreciate Kudos,