Many to Many Virtual Relationship with KEEPFILTERS

[Anonymous]

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!

[jdbuchanan71]

Hello @Anonymous 

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"

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/

[Anonymous]

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? 

[jdbuchanan71]

Hello @Anonymous 

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.