cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Anonymous
Not applicable

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

@Anonymous  Thanks..

6 REPLIES 6
Anonymous
Not applicable

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.

Anonymous
Not applicable

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

@Anonymous  Thanks..

Super User

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

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

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

Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors