cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
luiscb Member
Member

Many to Many Virtual Relationship with KEEPFILTERS

Hello everyone,

Here is a simplified Model I am working with, which has to continue in the state is being shown (meaning to relationships can be changed/added).

My Idea is to make a two measures to count distinct of Orders_Type in Fact1 but able to filter by the Analysis dimension in Fact2.
the ID_Mapping table: one ID_Fact2 can have may ID_Fact1s.

Since I cannot make the link between the mapping table & Fact2, I was trying with several combinations via simulating a virtualrelationship, but it does not work so far. Is it possible to achieve what I want with something like this? Although this one would basically work if the base measure would be on Fact1.

 
CALCULATE(CALCULATE(DISTINCTCOUNT(Fact1[Orders_Type]),
,INTERSECT(VALUES('Fact2'[ID]),VALUES(ID_Mapping[ID_Fact2]))),KEEPFILTERS('Fact2'[Analysis]="Mean"))


Is it possible to achieve this with a measure and leveraging virtual relationships, or the only way is to move that attribute to Fact1?

Thank you!

 

 
3 REPLIES 3
Super User
Super User

Re: Many to Many Virtual Relationship with KEEPFILTERS

Hello @luiscb 

You can popagate a filter from one table to another in a measure using TREATAS.  In my example, the ID is coming from TableA and you can see that [Sum A] is fine but [Sum B] is not filtered.  Using TREATAS in [Sum B TREATAS] we get the desired result"

TreatAsModel.jpg

TreatAsTable.jpg

Sum B TREATAS = 
CALCULATE(
    SUM ( TableB[Value] ),
    TREATAS( VALUES ( TableA[ID] ),TableB[ID] )
)

I have attached my sample file for you to look at.  The thing to keep in mind is, the first part of the TREATAS is the filter you are trying to replicate.  That is why we use VALUES ( TableA[ID] ) so we get the unique list values in the column.  That list is then applied to the TableB[ID] column and gives us the filtering.

 

Here is an article from the team at SQLBI.com discussing the topic.  https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

 

luiscb Member
Member

Re: Many to Many Virtual Relationship with KEEPFILTERS

Hey,

Thank you for the reply, my question is, like in my Model, the IDs from both FACT are distinct from one another, they do not match.
Thus having the Mapping table in the middle.. So we can have this TREATAS to spread from Fact1 to Mapping and from Mapping to Fact2? 

Super User
Super User

Re: Many to Many Virtual Relationship with KEEPFILTERS

Hello @luiscb 

If you have a lookup table sitting over your two fact tables you don't need to use TREATAS to pull data from both tables.  You put the attributes in the lookup table that you need.  Say you are working with customers.  One fact table has data buy Customer #, the other has data by Customer Email.  In you lookup table you put both Customer # and Customer Email along with the other thing you want to filter by (state, region, industry, etc).  Then you link the lookup table to both your fact tables but pull your category fields into reports from the lookup table.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 50 members 1,117 guests
Please welcome our newest community members: