Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
suman1985
Helper I
Helper I

In DAX, Slicer selected MULTI value is not working on the filter function.

Hi team,

I have written a DAX that will return a new table. The dax code will split the comma delimited column to rows and then have to filter the table based on the slicers selection criteria. Below code is giving the new table only when the manual input is given.If I do it dynamically from the Slicer selected value no results are showing.

The highlighted line is not working. and I wanted to filter the table by multivalues from the slicers.

I am attaching the sample pbix file.

Appreciate the help.

 

New table calculated from DAX:

CalulatedResultTable =

VAR _brand=SELECTEDVALUE(DimBrand[Brand])
VAR _sub=SELECTEDVALUE(DimSub[Sub])
VAR _Event=SELECTEDVALUE(DimEvent[Event])
VAR _task=SELECTEDVALUE(DistinctTasks[Distinct Tasks])

VAR MaxNames = CALCULATE(MAXX('Data', LEN(Data[Tasks]) - LEN(SUBSTITUTE(Data[Tasks], ",", "")) + 1))
VAR SplitTable=
                    FILTER(
                            GENERATE(
                                        Data,
                                        ADDCOLUMNS(
                                           
                                            GENERATESERIES(1, MaxNames, 1),
                                            "SingleName", TRIM(PATHITEM(SUBSTITUTE(Data[Tasks], ",", "|"), [Value]))
                                        )
                                    ),
                                    NOT(ISBLANK([SingleName]))
                            )
                   
 //VAR Result= FILTER(SplitTable,CONTAINSSTRING([SingleName],_task) && Data[Brand]=_brand && Data[Sub]=_sub && Data[Event]=_Event)  //NOT WORKING WHEN VARIABLE ARE ADDED            
//Here on the filters i have manually given single value to get results but i need to input multivalues 
VAR Result= FILTER(SplitTable, Data[Brand]="AA" && Data[Sub]="Free" && Data[Event]="Group1" && [SingleName]="a")
RETURN
Result
 
INPUT DATA:
BrandSubEventTasksAmount
AAFreeGroup1a,b,c100
AANormalGroup2b,c150
AAFreeGroup3a,c200
BBNormalGroup4b,d130
BBFreeGroup5a,c,d50
CCFreeGroup6d250
CCFreeGroup7c,d300
DDNormalGroup1a,d100

 

Expected result based on the above dax:

BrandSubEventSingleValueAmount
AAFreeGroup1a100
1 ACCEPTED SOLUTION

Thanks @nirali_arora This option worked...

View solution in original post

2 REPLIES 2
nirali_arora
Resolver II
Resolver II

For comma separated column, you can try the following solution, it will work better.

Power BI- Text Part slicer to filter/search text - https://youtu.be/MKKWeOqFG4c
https://medium.com/@amitchandak/power-bi-search-text-parts-in-text-ad2f9783ffa2

 

Thanks @nirali_arora This option worked...

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.