Forum Discussion
Check value in multiple tables
Hi Guys. Seeking help for the following scenario.
I have a table "Area" which has a list of locations. This table has one to many relationship with multiple tables by location. I was wondering if there is a measure that I can use that basically tells me if there is a value for a particular location in any of the connected tables.
As I am using the location from Area as a filter visual, I wanted to hide/filter out the locations if there is no value in any of the tables. So if there is no value in any of the tables, hide that location value; if there is a value then show up in the filter.
Thanks for your help.
azakir , I would not recommend that, but create a single measure
M1= countrows(Table1) + Countrows(Table2) + Countrows(Table2)
This can slow down the slicer and report. So used only if this works fine
Thanks amitchandak
this helped. just changed the formula a little bit. instead of countrows, I used counta(table1[location) + counta(table2[location]) etc.
4 Replies
azakir , If you want to control the slicer value
Create a measure on many side table
M1= countrows([By location])
Now use a visual level filter of the slicer and check this measure is not blank. This can be done with other visuals too
Hi amitchandak
thanks for your reply, does that mean I have to repeat this process for all the tables?
I was wondering if there's an easier way to check if the value exists for a location in any of the multiple tables, and if it does, only then display it as a filter.
azakir , I would not recommend that, but create a single measure
M1= countrows(Table1) + Countrows(Table2) + Countrows(Table2)
This can slow down the slicer and report. So used only if this works fine