Forum Discussion
Filter data based on another visual table
Hi,
This seems pretty simple to do but for some reason I couldn't figure it out. I have 3 calculated tables as below and my goal is to have table B/C only show the Account CSN that's showing in table A. There's already a relationship created to connect these 3 tables by Account CSN (I created a bridge table to link these three tables) however it still didn't work as expected.
Hope my description is clear enough. Thanks in advance for the help!
Anonymous thanks. Is the relationship single or both cross filter direction? See if this post helps explain why that matters
https://radacad.com/one-dimension-filters-another-dimension-in-power-bi
For your example i suggest you create measures using USERELATIONSHIP instead of calculated tables. You'll need inactive relationships to make this work.
5 Replies
Anonymous thanks. Is the relationship single or both cross filter direction? See if this post helps explain why that matters
https://radacad.com/one-dimension-filters-another-dimension-in-power-bi
For your example i suggest you create measures using USERELATIONSHIP instead of calculated tables. You'll need inactive relationships to make this work.
Anonymous
What does the data model look like?
Why have you created calculated tables? Can you just use Measures to calculate the Churn, Renew, New, etc.
If you do that, then you can use the measures as Visual level filters.
Hope that helps a bit, please share the DAX for the calculated table and your data model relationships view if you need more help.
AllisonKennedy thanks for your response. I created several calculated tables because there are different date columns in my raw data set. Basically, I had to group new measures by settlement start date and group expire measures by settlement end date.
So for table A, the DAX looks like:
CALCULATETABLE(SUMMARIZE(RAW DATA,
RAW DATA[Settlement End Date],RAW DATA[Account CSN],"Expire",SUM(RAW DATA[Expire]))Table B:
CALCULATETABLE(SUMMARIZE(RAW DATA,
RAW DATA[Settlement Start Date],RAW DATA[Account CSN],"New",SUM(RAW DATA[New]))The data model relationship is quite simple, there's only one Account CSN (unique value) table to connect them all. Happy to provide more information or perhaps a sample pbix file if this is still not clear enough.
Thanks AllisonKennedy! I was able to apply both cross filter direction in my data model and make it work.
Anonymous Glad you have it working. Do be care with the both cross-filter direction. It will add lots of ambiguity to your model. Watch the video associated with this blog if you have time, it explains it well with simple sample data for demonstration: https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/