How to surpass single filter direction?

[CasaMoreira]

Hello everyone
I have the below datamodel:

I have a table visualization with the content of table 4, and a slicer with the content of table

Selecting a value of table1, I want to see the related values of table 4

This is not working because of the relationship direction from table 2 and table 3.

Using a bi-directional filter is not an option (ambiguity).

How can I accomplish this?

[tharunkumarRTK]

@CasaMoreira 

You can use CROSSFILTER dax function and tweak the directionality for that one particular calculation to 'BOTH' instead of SINGLE.

https://dax.guide/crossfilter/

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[CasaMoreira]

Thank you.

can you give me an example? Where should I create the measure with the CROSSFILTER?

[tharunkumarRTK]

In power bi measures are bind to any table, you can create measures in any table. I hope this answers your question.

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[CasaMoreira]

The problem is that creating this measure will not return a single value, but a list of connected values... So creating this measure in Table 2 will not work. Doesn't it make sense?

[tharunkumarRTK]

I corrected the measure and added aggregation, please copy it again and test it.

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[CasaMoreira]

Creating the measure below in table 2 doesn't work because of the "MAX" -> only returns one item... 

Should we create it in other table?

[tharunkumarRTK]

@CasaMoreira 

Yes this measure will only retrieve one scalar value. If you want to retrieve all values and consider modifying your model and follow star schema parctices then you would not require crossfilter function.

[tharunkumarRTK]

@CasaMoreira 

Assuming your related table1_2 and table 2 with value2id columns

CALCULATE (
            MAX(table4[value2id]),
            CROSSFILTER ( table1_2[value2id], table2[value2id], BOTH )
        )

Your model is deviating from the principles of star schema, and it is not a good practice 🙂

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[CasaMoreira]

I undestand....

But I have filters (table 2 and 3) that are set in table 1

And these filters are available in table 4... So I couldn't find any other way to do this...

For the expression shared:

CALCULATE (
            table4[value2id],
            CROSSFILTER ( table1_2[value2id], table2[value2id], BOTH )
        )

I tried to add this as a measure in Table 2, but it's not allowing the first paramenter of the CALCULATE function "table4[value2id]"...

Can you please help here? 😞