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

[suman1985]

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:

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

[Anonymous · Accepted solution]

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.

[Anonymous]

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

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.

[suman1985]

Thanks for the Reply @Anonymous 

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

[Anonymous · Accepted solution]

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.

[suman1985 · Accepted solution]

@Anonymous  Thanks..

[ChiragGarg2512]

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.

[suman1985]

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