Forum Discussion
Helper IIFilter visuals without direct relation (from many to 1 to many)
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.
Super UserHi 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.
Helper IIOk 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.