Forum Discussion
cross filtering columns in the same table
This is so basic apologies, I'm missing something.
I have a fact table containing organisations, they are grouped in a separate groups, there are mutliple entries for each organisation in the table. (The example below only shows one).
When a user selects the org name via a dropdown selector, I need the other visuals on the page to respond and filter by org group, not just a single selected org name. You can turn on and off the interactions. I have tried putting the org group into the filter, Top N etc. I can get the Org group to display in a visual but I can't use it to drive the filter on another visual. When I change the dropdown selection the group appears in the visual filter selection but I can't seem to use it as a filter without manually selecting it.
https://www.dropbox.com/s/qcx6h5ovisx10on/cross%20group%20filter%20Question.pbix?dl=0
I've posted an example file on the following link.
Example file.
you will need second table for this - otherwise filter you put on Org Name will overwrite one coming from the Org Group, tables shouldn't have joins
new table (named Slicer)Org Name Org Group Org1 1 Org2 1 Org3 2 Org4 2 Org5 3 Org6 3 Org7 4 Org8 4 Measure = CALCULATE( SUM('Table1'[Score]), INTERSECT(ALL('Table1'[Org Group]),VALUES(Slicer[Org Group])) )
4 Replies
you will need second table for this - otherwise filter you put on Org Name will overwrite one coming from the Org Group, tables shouldn't have joins
new table (named Slicer)Org Name Org Group Org1 1 Org2 1 Org3 2 Org4 2 Org5 3 Org6 3 Org7 4 Org8 4 Measure = CALCULATE( SUM('Table1'[Score]), INTERSECT(ALL('Table1'[Org Group]),VALUES(Slicer[Org Group])) )Thank you Stachu
I've tried it on the PBIX file I linked to above.
It almost works, it correctly filters the graph so that only the Orgs in the selected group are displayed but they all return the same single value. How can I use the cross filtering but display the individual Score.
I've tried placing almost every attribute in every box. Any ideas?
the 'Org Name' in the chart should come from the Table, not the Slicer, that should give the proper sum - is this the case?
so - for filtering you use Slicer[Org Name], in the visual you use Table[Org Name]
EDIT - spelling
