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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors