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, Filter a table based on the slicers and store it in a variable and return

Hi Team,

I am new to DAX and learning.Please help on the below request.

I wanted to Filter the witbi table based on the multi value slicers(Brand,Sub,Event) from the Same table and stored this filtered table in a variable and in the same dax,i will be using this table variable to do further analysis.. Appreciate the help.

witbi Table:

 

BrandSubEventTasksAmount
AFreeGroup1a,b,c100
ANormalGroup2b,c150
AFreeGroup3a,c200
BNormalGroup4b,d130
BFreeGroup5a,c,d50
CFreeGroup6d250
CFreeGroup7c,d300
DNormalGroup1a,d100

 

DAX: --givng results blank table

NamesSplitTEST =

 VAR FilteredTable=FILTER(witbi,
                            witbi[Brand] in VALUES(witbi[Brand]) &&
                            witbi[Sub] in VALUES(witbi[Sub]) &&
                            witbi[Event] IN  VALUES(witbi[Event])
                        )
Return
FilteredTable

 

2 ACCEPTED SOLUTIONS

Hi @suman1985 ,

For measure, could not return the measure with a table, must return a value.

If you want to return a table with dax, you should create calculate table.

The examples I've provided are just to illustrate how to create similar measure.

 

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-binbinyu-msft
Community Support
Community Support

Hi @suman1985 ,

Please try below steps:

1. need create three new tables to add a slicer

Brand = VALUES('Table'[Brand])
Event = VALUES('Table'[Event])
Sub = VALUES('Table'[Sub])

2. create a measure with below dax formula

Measure =
VAR _bd =
    SELECTEDVALUE ( Brand[Brand] )
VAR _et =
    SELECTEDVALUE ( Event[Event] )
VAR _sub =
    SELECTEDVALUE ( Sub[Sub] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), [Brand] = _bd && [Event] = _et && [Sub] = _sub )
RETURN
    COUNTROWS ( tmp )

3. visualization

vbinbinyumsft_0-1694588821456.png

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for the Reply @v-binbinyu-msft 

I wanted to return a table here not the count of table.

Hi @suman1985 ,

For measure, could not return the measure with a table, must return a value.

If you want to return a table with dax, you should create calculate table.

The examples I've provided are just to illustrate how to create similar measure.

 

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-binbinyu-msft  Thanks..

ChiragGarg2512
Solution Sage
Solution Sage

A calculated table cannot be filtered by slicer. Create a table visual of the data and apply the columns of the table into the slicer, it will filter the data in table visual.

Hi @ChiragGarg2512 

 

Thanks for the reply.

Unfortunately, I dont need to create a table visual. 

I want this table to be filtered based on the slicers and want to store this in a variable for further dax analysis 

Is there a better way to write the DAX for this scenario.

 

Thanks

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