topic Re: List of values with filter, kindly help me! in DAX Commands and Tips

List of values with filter, kindly help me!

Anonymous — Mon, 17 Feb 2025 08:33:56 GMT

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?

Re: List of values with filter, kindly help me!

FarhanJeelani — Tue, 18 Feb 2025 05:18:42 GMT

Hi Anonymous ,

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,

Re: List of values with filter, kindly help me!

Anonymous — Mon, 17 Feb 2025 13:28:50 GMT

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

Re: List of values with filter, kindly help me!

FarhanJeelani — Mon, 17 Feb 2025 13:52:14 GMT

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]))

Re: List of values with filter, kindly help me!

Anonymous — Tue, 18 Feb 2025 07:02:15 GMT

Thanks for the replies from FarhanJeelani.

Hi Anonymous ,

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.