Slicer related filter

[andrearuggeri]

Hello everybody,

This is my model

I have built some visuals based on Strutture table where I count the number of times the field Struttura (in x axis) is Presente (yes/no quesiton) so the whole visual is filtered for yes for Presente field. And this is the easy part.

What I need to achieve is to have a slicer with values from ProdStrutture.ItemModel that filters the above visual.

As from the above picture, the relation between the two tables is the Visite table, so in my mind the scenario should be

• I select a value from the ProdStrutture.ItemModel slicer
• The data/table ProdStrutture get filtered and I have the CallId that contain the ItemModel selected
• The data/table Strutture should be filtered by the same CallId

However it doesn't work.

I've read some guides/posts and tried to change the relation between ProdStrutture and Visite bi-directional, with no luck: it does something, it filters for something but it doesn't take care of the above Presente field in the count of Struttura, it counts both yes and no. But I feel this is not the right way...

Whay should I do?

Thanks a lot in advance

[andrearuggeri]

I'm now trying a completely new approach, but again I please need help 🙂

I've created a merged table taking data from Strutture and ProdStrutture, so that now I have all needed data in the same table.

To make the whole thing working, I need to do a magic: I need to create a Yes/No slicer related to Presente field (easy part)

The hard part is that this slicer should change selected value based on the ItemModel slicer.

If ItemModel has something selected, Present slicer should be set to "Yes"; if ItemModel has nothing selected, Present slicer should be set to nothing/all (neither Yes nor No)

Is it possible and how?

Thanks a lot

[pmay]

I'm approaching the end of my ability, so if someone else wants to solve this, please go ahead!

In the table showing too many values, when you remove the filter for that visual on "Presente", you get the right result?

Then the measure I would write would be something like this, but I'm not 100% sure it would work.

Measure = CALCULATE (MAX('ProdStrutture'[POS]), 'ProdStrutture'[Presente] = "Yes").  I would replace the POS column in that visual with this measure. 

I fear it may only return a maximum of 1 row, but I hope because you have ItemName in there, it would add additional rows when you have different values in there.  So test it on a few different slicer selections to make sure you see the expected results.  You would then no longer need the visual filter, as your measure is doing the filtering now.

I really hope this is a solution, because I don't know what else to try.  I guess the language difference in your model is also making it difficult for me to assume/understand what is in each column.

[andrearuggeri]

Not working, since the table showing too many results is Strutture, and i cannot (or not able to, I'm getting errors) build a measure where I filter for values from another table.

[pmay]

Try this for the DAX then:

Measure = CALCULATE (MAX('Strutture'[POS]), FILTER('Strutture', RELATED('ProdStrutture'[Presente]) = "Yes").

[andrearuggeri]

Neither working, it says 'ProdStrutture'[Presente] not exist (wrong) or not related (wrong) 😞

[pmay]

I do not know why the relationship doesn't work as bi-directional, but you're right - that's what you need.  Currently, when you select a value from ItemModel slicer, ProdStrutture table would be filtered but it will do nothing to the Visite table, because the relationship doesn't work in that direction.

A simple solution is to open PowerQuery, go into your Visite table, and click on Merge Queries.  Select ProdStrutture as the table to merge in, and select the columns on which they are linked.  Then once you've done the merge, expand the ItemModel column (and any others you may need) directly in the Visite table, and use ItemModel from the Visite table as your slicer.

This may not be best practice, it kind of depends on what these columns mean and how much data you have, but it's a quick & dirty fix.

[andrearuggeri]

Unfortunately this is not a way I can follow, too much data and too much other "things" related to Visite that will be messed up 😞

The real strange thing is that bi-directional relation partially works but in a weird way.

If you can please take a look at this video https://nimb.ws/AOVHiS you will see with your eyes.

The first table visual in the video shows data from Strutture, the second from ProdStrutture. Everything is as described in my first post. Relation is bi-directional.

As you can see, when i select a certain ItemModel from the slicer, the ProdStrutture correctly shows just one POS, as that very model is present only there, while the Strutture shows filtered data but with wrong criteria, showing more POS.

Any idea?

[andrearuggeri]

I've partially figured out what should be the problem.

When I select a model from the slicer, the bi-directional relation works and the data/table Strutture actually get filtered, taking in consideration only the Strutture.CallId where that very ProdStrutture.ItemModel data are collected.

The missing part is the Presente filter that I use in the visual.

I should find a way (DAX, measure...?) to do the following

• I select a model in the slicer
• ProdStrutture get filtered by that ItemModel only where Presente is "yes" (missing part)
• I get ProdStrutture.CallId that is used to filter Strutture

Any idea to achieve this?

Thanks a lot