cancel
Showing results 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

Helper I

## In DAX, Filter a table based on the slicers and store it in a variable and return

Hi Team,

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:

 Brand Sub Event Tasks Amount A Free Group1 a,b,c 100 A Normal Group2 b,c 150 A Free Group3 a,c 200 B Normal Group4 b,d 130 B Free Group5 a,c,d 50 C Free Group6 d 250 C Free Group7 c,d 300 D Normal Group1 a,d 100

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
Community Support

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.

Helper I
6 REPLIES 6
Community Support

Hi @suman1985 ,

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

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.

Helper I

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

Community Support

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.

Helper I

@v-binbinyu-msft  Thanks..

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.

Helper I

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

Announcements

#### 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.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### 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
Top Kudoed Authors