Forum Discussion
One common filter for two columns
Hi experts.
I have a data model that looks like this
From the model, I have a common filter (DimSport), for all the measures. From AfiPartition I can filter with dates. SportsInformation, has data related to the quantity of users who belongs to a sport; SportIntersection, has information about quantity of users that belong to SportA and Sport B.
I'm trying to have one single selector for Sport Dimension, i.e: if I select "Balonmano", "eSports" and "Motociclismo", the table should return data related to those sports:
From "Sport A Label" column, it is showing the values selected from filter, but not from "Sport B Label", it is showing values like "Baloncesto" or "Futbol Sala". I only want to exlude thos from the table.
I have tried several measures to filter the column "Sport B Label" with no success, i.e:
Pct. % =
CALCULATE( DIVIDE(sum('Sport Intersect'[QtUsersIntersection]); sum(SportInfo[QtUsersSportTotal]);0);
FILTER('Sport Intersect'; 'Sport Intersect'[Sport B Label] = ALLSELECTED(DimSport[NaSport]))
)
Pct. % 2 =
CALCULATE( DIVIDE(sum('Sport Intersect'[QtUsersIntersection]); sum(SportInfo[QtUsersSportTotal]);0);
VALUES(DimSport[NaSport])
)
Please see the attached file here
I did try to relate DimSport[IdSport] with SportsInformation[IdSportA] and SportsInformation[IdSportB] but it didn't work as there's one inactive relation.
Any suggestion? I'm really stuck with this.
Edit:
I've been doing some test and write some formulas and I realized that what I want is this:
Pct. % 3 =
CALCULATE( [All Pct. %];
KEEPFILTERS(
FILTER(ALL('Sport Intersect'[Sport B Label]); 'Sport Intersect'[Sport B Label] = "Balonmano" || 'Sport Intersect'[Sport B Label] = "eSports" || 'Sport Intersect'[Sport B Label] = "Motociclismo")
)
)Of course, It should consider the selections of DimSport and propagate it to the column 'Sport Intersect'[Sport B Label], instead of hardcode the value. How can I do this?
Hi Anonymous ,
I saw that the column of Sport A Label is not filtered either. I created two measures to implement filter them. You could check if the effect is what you want.
Sport A = VAR A = CALCULATE(MAX('Sport Intersect'[Sport A Label]),FILTER(RELATEDTABLE(DimSport),DimSport[NaSport] = MAX('Sport Intersect'[Sport A Label]) )) return IF(ISFILTERED(DimSport[NaSport]),IF(A = BLANK(),BLANK(),A),MAX('Sport Intersect'[Sport A Label])) Sport B = VAR A = CALCULATE(MAX('Sport Intersect'[Sport B Label]),FILTER(RELATEDTABLE(DimSport),DimSport[NaSport] = MAX('Sport Intersect'[Sport B Label]) )) return IF(ISFILTERED(DimSport[NaSport]),IF(A = BLANK(),BLANK(),A),MAX('Sport Intersect'[Sport B Label]))
2 Replies
Hi Anonymous ,
I saw that the column of Sport A Label is not filtered either. I created two measures to implement filter them. You could check if the effect is what you want.
Sport A = VAR A = CALCULATE(MAX('Sport Intersect'[Sport A Label]),FILTER(RELATEDTABLE(DimSport),DimSport[NaSport] = MAX('Sport Intersect'[Sport A Label]) )) return IF(ISFILTERED(DimSport[NaSport]),IF(A = BLANK(),BLANK(),A),MAX('Sport Intersect'[Sport A Label])) Sport B = VAR A = CALCULATE(MAX('Sport Intersect'[Sport B Label]),FILTER(RELATEDTABLE(DimSport),DimSport[NaSport] = MAX('Sport Intersect'[Sport B Label]) )) return IF(ISFILTERED(DimSport[NaSport]),IF(A = BLANK(),BLANK(),A),MAX('Sport Intersect'[Sport B Label]))Thanks v-xuding-msft, it did work. As you mention, Sport A Label column is not filtered either. I've seen that you changed the relationship of tables to bidirectional. It is needed to do this in order to get right results?
Also I'm trying to understand the measures you have created. Why is not possible to use the function VALUES() instead of MAX()? With the function values I'm obtainign the values picked on the DimSport slicer, Isn't it?