Using filtered values in slicer as a variable to filter other tables using DAX functions

[MohannadSh1996]

I have many tables with Ring column.

Ring

R1

R2

R3

R4

I want to have a slicer for Ring to filter all the graphs without making any relationships between the tables and with supporting multiple selections.

I'm thinking of using all the selected values in the slicer of Table1 as a parameter to filter Table2 values with DAX functions, like:

FilteredValueFromTable2 = 
CALCULATE(SUM('Table2'[Value]),FILTER(Table2, Table2[Ring] == All selected values from Table1 slicer))

 
But i don't know what to replace "All selected values from Table1 slicer" with.

i would appreciate if someone can help 😊

[Anonymous]

Hi @MohannadSh1996 ,

How about use VALUES() function.

Measure = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Ring] in VALUES('Table (2)'[Ring])))

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@MohannadSh1996 , try like

FilteredValueFromTable2 =
CALCULATE(SUM('Table2'[Value]),FILTER(Table2, Table2[Ring] in Allselected(Table1[Ring])))

[MohannadSh1996]

@amitchandak, What about: 

CALCULATE(SUM('Table2'[Value]), Table2[Ring] IN VALUES(Table1[Ring]))

[amitchandak]

@MohannadSh1996 , try and let us know the results

[MohannadSh1996]

@amitchandak, This solution returned an error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

[Mariusz · Accepted solution]

Hi @MohannadSh1996 

Try this.

FilteredValueFromTable2 = 
CALCULATE(
    SUM('Table2'[Value]),
    TREATAS( VALUES( Table1[Ring] ), Table2[Ring] )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[MohannadSh1996]

Hi @Mariusz, Thanks for your post.

It seems that this worked, but it resulted a problem with graphs load dates. e.g., Some graphs have DataTime range (1st of March - Today data) but after adding TREATAS, they show a trimmed time range in the graphs, how is this related to the function?  

[Mariusz]

Hi @MohannadSh1996 

Never happened to me before with any Function as it is not related, but you can format your date time column as below.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[MohannadSh1996]

@Mariusz, The problem was that the Table1 is missing one of the Ring values that was exist in the other table, so, all the date related to this Ring will not be shown which will cause a trimming of data. (Fixed)

@amitchandak& @Anonymous, thanks for your answers 😊