Filter based on Table

[Yaya1990]

Hi Dears,

I am working on a dashboard reading data from 4 different sensors, and each has a different table, is there any way I can filter my data based on a table, or how can I create a filter to see the data of the specific sensor.

Your help will be highly appreciated.

Regards,

Fahad

[SanketBhagwat]

Hi @Yaya1990 .
You can create a custom table using 'Enter Data' and add your table names in there, drag a slicer and add the custom table values in it i.e. now in your slicer you will have 4 different table names.

Now in your measure pass those tables values in the calculation using SELECTEDVALUE.


For example, 
test=
var a=SELECTEDVALUE(Table[TableNames])
return
SWITCH(TRUE(),a=Employee_Table,[Employee_Count],b=Sales_table,[SumSales],c=IT_Table,[CountMeasure],d=Healthcare,[Measure])

Now add your test measure inside a KPI or any other visual you want


Please let me know if it works.

Thanks,
Sanket


If this post helps, then mark it as 'Accept as Answer' and give it a thumbs up

[Yaya1990]

Hi, let me give you my table names

Suppose,

Table 1

Table 2

Table 3

Table 4

Each table has a column named as difference where i have calculated a value. 

I need to do this (Difference Table 1 + Difference Table 2 + Difference Table 3 + Difference Table 4) and then show it on a bar chart.

once this is done, i need slicer filter, where if i click Table 1, it only shows me the data for table 1 and hide other information.

Please help me if you can as this is very imp dashbaord, and let me know if you need more clarification.

Regards,

Fahad

[SanketBhagwat]

Click on Enter Data and create a custom table.
Let's say table test; give a column name as 'column_name' and under that provide your 4 table names i.e. Table1 to Table4.

Add this table to a slicer visual.


Create a sum of Difference measure;
Sum_Diff=Difference_Table1+Difference_Table2+Difference_table3+Difference_table4.

Create a measure which will show your difference values;
Difference=
var a=Test['column_name']
return
SWITCH(TRUE(),
a=Table1,[Difference_Table1],a=Table2,[Difference_Table2],a=Table3,[Difference_Table3],a=Table4,[Difference_Table4],[Sum_Diff])

Let me know if it works.

If this post helps, then mark it as 'Accept as Answer' and give it a thumbs up

[Yaya1990]

i cannot get the first line working (var a=Test['column_name']), it is not reading the table, am i doing something wrong

[SanketBhagwat]

If you have properly created the custom table using 'Enter Data' then you won't have any problem passing Table name inside the SELECTEDVALUE function.

Cross check again.

Thanks,
Sanket

[Yaya1990]

Yes when using, the selected value, it is showing it in the dax, but when i use switch command, i dont see any of my tables: 

is this becuase Create a sum of Difference measure;
(Difference_Table1,Difference_Table2,Difference_table3,Difference_table4) are calculated columns not a measure.

if they are columns what could be the solution? i would highly appreciate.

[Yaya1990]

[SanketBhagwat]

What is a=1?
Is 1,2,3,4 name of your columns or do have names as Table1,Table2,Table3 and Table4?

If Difference1,Difference2.. are columns then you will need to specify an aggregation as 
Difference=
var a=Test['column_name']
return
SWITCH(TRUE(),
a=Table1,SUM([Difference_Table1]),a=Table2,SUM([Difference_Table2]),a=Table3,SUM([Difference_Table3]),a=Table4,SUM([Difference_Table4]),[Sum_Diff])

I hope that will solve your problem.
Else instead of columns you can create DifferenceTable1,DifferenceTable2... as measures and not columns.

Thanks,
Sanket

[Yaya1990]

a = 1 , 1,2,3,4 is the name of my columns

[amitchandak]

@Yaya1990 ,

Calculate(Sum(Table[Value]), filter(Table, Table[Column] in values(Table2[Col]) ) )

Also, check

https://docs.microsoft.com/en-us/dax/treatas-function

[Yaya1990]

Hi, thanksyou for replying, I need a filter in slicer showing four different tables, once i click on one it only displays the data for that sensor? is it possible?