Filter visuals without direct relation (from many to 1 to many)

[elflakoconk]

Hello everyone.

I've splited a single table and now I have a model with 4 tables. All have the same rows, the difference is that I'm splitting by topic.

Now I'm using 1 as a dimession table called 'Data'. For the other 3 I have applied unpivot, because some columns in some rows are empty and I want to display this information in visuals.

When I create the model got 3 many to 1 relations (1 is for 'Data' table). Like an inverse star schema.

But here is where I'm stucked.

I'm trying to make this without using the BOTH direction of the relationship:

So I tryied using CROSSFILTER() without success.

I'm attentive to read your comments about this and how should be the best practice, because there's a lot more tables with the same condittion, about 36 topics all in a single table with 265 columns, and I really need to show the filtering interaction.

Thanks a lot!

In the link you'll find a PBI file with some data as example:

https://1drv.ms/u/s!Ap6grYRZ2cfGr0eNOiwLFgNmoRKA?e=7333Lk

[MFelix · Accepted solution]

Hi @elflakoconk ,

Your issue is that you are making the Crossfilter relationship on the incorrect path, when you are summing the analfabetism you need that measure to be crossfiltered by the other tables so that should be the ones you use on your crossfilter in this case salud and discapacidad.

Moreover since you are using direct columns on your other visualizations they will not get filter since you are not making the cross filter to be applied.

Create the following 3 measures:

Analfabetismo = 
CALCULATE(
    SUM('Analfabetismo'[Valor]),
   CROSSFILTER(
        'Data'[Bloque],
        'Discapacidad'[Bloque],
        Both
    ),
    CROSSFILTER(
        'Data'[Bloque],
        'Salud'[Bloque],
        Both
    )
)

discapacidade = 
CALCULATE(
    SUM('Discapacidad'[Valor]),
   CROSSFILTER(
        'Data'[Bloque],
        'Analfabetismo'[Bloque],
        Both
    ),
    CROSSFILTER(
        'Data'[Bloque],
        'Salud'[Bloque],
        Both
    )
)

Salud = 
CALCULATE(
    SUM('Salud'[Valor]),
   CROSSFILTER(
        'Data'[Bloque],
        'Analfabetismo'[Bloque],
        Both
    ),
    CROSSFILTER(
        'Data'[Bloque],
        'Discapacidad'[Bloque],
        Both
    )
)

Has you can see each of the metrics is being crossfiltered by the other two table that way you can make it work, now use this metrics on the charts result below and in attach PBIX file.

[elflakoconk]

Ok ok ok. Thank you!

Just another question, considering that I must use CROSSFILTER() for each relation (same number as relations I got), could be better to set de BOTH direction cardinality in the data model?

For example, lets think in other 10 tables (total 13):

I must create a meassure using 12 times the CROSSFILTER() function to allow a total interaction, that for each table. 

I undertand that using BOTH direction in the relationship is not the best practice (dat model), but in this case, does this rule still apply?

[elflakoconk]

Well, so I create 2 PBI files one with crossfilter direction set as Both in the data model (from now will call it 1) an the other using the crossfiler function (will call it 2), here is the report view:

This is the data model for 1:

and this the data model for 2:

So I used the CROSSFILTER function for each table:

Disca: = 
CALCULATE( 
SUM('Disca'[Valor]), 
CROSSFILTER('Data'[Bloque],Disca[Bloque],Both), 
CROSSFILTER('Data'[Bloque],Eco[Bloque],Both), 
CROSSFILTER('Data'[Bloque],Edad[Bloque],Both), 
CROSSFILTER('Data'[Bloque],Escola[Bloque],Both), CROSSFILTER('Data'[Bloque],Fabetis[Bloque],Both), 
CROSSFILTER('Data'[Bloque],Nace[Bloque],Both), 
CROSSFILTER('Data'[Bloque],NoAsiste[Bloque],Both), CROSSFILTER('Data'[Bloque],Salud[Bloque],Both), 
CROSSFILTER('Data'[Bloque],Servs[Bloque],Both), 
CROSSFILTER('Data'[Bloque],TIC[Bloque],Both) 
) 

 

Here is the result of the performance analyzer:

1

2

What I found is that 1 file has a better performance than number 2 (crossfilter function), also, I can't achieve to filter the map visual clicking on other visual in file 2, like this:

(image from file 1)

This is the result of performance analyzer clicking on a visual:

So MAYBEin this case, is better to set de both direction in the data model for general, but when clicking a visual, the meassure is better, What do you think?

Here you can find the files:  PBI files 

[MFelix]

Hi @elflakoconk ,

The question about bi-directional filter is a best practice however you can implement it this depends on a lot of things especcially on your model and the results you want to achieve, in this case since you have a simple model with 1 fact table and several dimensions related directly to your main table the problems that could come from the filtering should be relatively simple to check.

Regarding the question about the performance I see that altough model 1 has a better performance that is marginal since we are talking about miliseconds and the visualization with the highest loading goes from 1.6 seconds to 1.7 seconds.

I believe you should opt for the option that get's you more confortable, in this case the low maintenance of the bi-directional filtering turn on and the metrics seems to be the best option for you.

Hope I have helped to sort some things.