Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sebastienke
Helper III
Helper III

What is the logic of Filtering using relationship ?

Hi, 

 

I have a case for which I cannot understand how Power Bi is thinking to get result...

 

I have this model with these relationship :

Please don't ask me for what this datamodel is used for, it is actually more complex than that but I have reduced the model to expose the problem

 

CALENDRIER 1-->* E&O linked by "month year"
E&O *<-->1 UPC vs MAT linked by "UPC"

UPC vs MAT 1<-->1 MASTERDATA linked by "MAT"

MASTERDATA 1-->* STOCK linked by "MAT"

STOCK *<--1 CALENDRIER linked by "month year"

 

sebastienke_1-1630086136997.png

 

According to the directions of relationships, we can suggest that STOCK will be filtered on both sides (from MASTERDATA and from CALENDRIER).

 

So when I create a table to filter by month the sum of "stock"  in STOCK ((it is not a measure) and the distinct count of "Mat" in MASTERDATA, I have this result :

sebastienke_2-1630088759187.png

 

When I change the relationship

E&O *<-->1 UPC vs MAT linked by "UPC"
by 
E&O *<--1 UPC vs MAT linked by "UPC"

 

For the same table, I have this result :

sebastienke_3-1630088952642.png

 

  • In the first case, the distinct number of Mat changes for each month because of the relationships between the two tables CALENDRIER and MASTERDATA.
  • In the 2nd case, I have the same distinct number of Mat for each month. Once again it is explained by the relationships between CALENDRIER and MASTERDATA (the direction between E&O and UPC vs MAT doesn't go in the same direction than the first case).

What I dont understand is why the Stock amount is not changed in both case.
What I was expected in the first case is that the sum of "stock" in STOCK would be filtered by the "mat" present in the table Masterdata (which as we can see are not the same for each month)....

Is someone able to explain this ?

 

Thank you for your help.

1 ACCEPTED SOLUTION
sebastienke
Helper III
Helper III

So, according to an expert I was talking to. 
When Power BI is confronting this kind of situation (table filtered by different tables), it will chose the shortest relation and will apply it instead of the others.

That's why my STOCK table is filtered by calendar (shortest relation) and not by MASTERDATA table (longest relation).

View solution in original post

19 REPLIES 19
sebastienke
Helper III
Helper III

So, according to an expert I was talking to. 
When Power BI is confronting this kind of situation (table filtered by different tables), it will chose the shortest relation and will apply it instead of the others.

That's why my STOCK table is filtered by calendar (shortest relation) and not by MASTERDATA table (longest relation).

sebastienke
Helper III
Helper III

no one in the community has the answer ? 😕

sebastienke
Helper III
Helper III

@Greg_Deckler  here is a popularization of my problem with fruits and vegetables.
If you have a look, it would be greatly appreciated.

sebastienke_0-1630287451275.png

 

sebastienke_1-1630287463607.png

 

sebastienke_2-1630287486237.png

 

sebastienke_3-1630287518447.png

 

sebastienke_4-1630287586670.png

 

sebastienke_5-1630287604656.png

NB : The column Stock is the sum of the field STOCK in the table STOCK

Please provide the sample PBIX file you have created with the fruits and veggies. You can share using OneDrive, Dropbox, Google Drive....





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






There is a problem with the model: the Crossfilter Table is filtering out values from the Masterdata Table because they are linked by the SubItem field which is limited in the Crossfilter Table:

2021-09-01.jpgModel.JPG

 

So whenever you reference/filter etc by the Crossfilter table, the Masterdata will be filtered for "peach" and/or "salad",  and the filter propagates down to the Stock Table. The other values will be therefore ignored. 

I would suggest you substitue the Crossfilter table by a new table which contains the unique values for Item, and link this field ina a one-to-many relationship with the E&O and Masterdata Table.

New Model.jpg

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown  Thank you for your help/analysis, but I still have the same question (or I didn't understand your answer).

 

Maybe I'm not clear when I explain the problem. Sorry about that.


Actually, as described, the crosstable should filter MASTERDATA and STOCK because in CROSSTABLE we have just "peach" and "salad". 

We can see in pictures of my former post that when I ask for each month to count the distinct SUBITEM :
- in MASTERDATA, the result is 2 for each month (peach and salad) because there is no notion of date in MASTERDATA and so the 2 subitems appear for all period. That part is logical.
- in STOCK, the result is 4 for January, 3 for February and 3 for March. Those results corresponds to the entire table. So, why STOCK has not been filtered by the SUBITEM coming from MASTERDATA (ie. peach and salad) ? I was expecting the following result : 2 for January (peach and salad), 0 for February, and 1 for March (salad).

(NB : My actual power bi is way more complex than that, and I agree that the example with fruit and vegetable doesnt make sense, but it was just to duplicate the problem with more simple and less data).

 

Thank you 🙂

Oh I see! My sincere apologies! I completely misunderstood the problem. 

Ok, the reason you are seeing these results is:
1) The relationship between E&O and Crossfilter is bi-directional:

The rows filtered by date in E&O propagate to Crossfilter which in turn filters Masterdata to only show the two sub-item values in Crossfilter. Result for Subitem masterdata = 2 (salad and peach filtered by Crossfilter).

 

bidir.JPG

 


2) The relationship between E&O and Crossfilter is single:
The filter context coming from the date table only filters the rows in the E&O and STOCK tables (since they cannot propagate to other tables due to the nature of the relationships in a single direction). 

Therefore the value returned in MASTERDATA has no filters on it = 7

single.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Thank you @PaulDBrown 

in the first case, you have explained the result of MASTERDATA and I agree with what you have exposed, but my question is about STOCK. Why the green relation in the picture below is not applied ?

 

sebastienke_1-1630518717450.png

 

That's the SECOND time I haven't read the question properly, Thank you for your patience @sebastienke .

And I confess I am as mystified as you are. It looks as though the filter propagation stops at the Masterdata table (and I have tried all sorts of ways to "see" what is going on).
However, there is a way to get the correct results using:

 

 

Summarize measure =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( STOCK, 'CALENDAR'[Month year], MASTERDATA[SUBITEM] ),
        "Test", [MEASURE OF STOCK]
    ),
    [Test]
)

 

 

Summarize.JPG

Since the Masterdata table is actually filtered by the propagation, this measure creates a virtual table which only contains the Subitem values "visible" (filtered) in the Masterdata table and  computes the sum of stock by the filtered dates.
The mystery, however, remains unresolved. We need the help of top gurus.
Any chance you can lend a hand @marcorusso @MattAllington ?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown I am reassured that i am not the only one to not understand what is going on.... but my problem is still not resolved 😂

 

You are right about "summarize".
Actually, it is by using it in one of my measures that i have detected a difference in the results.


At the end, what I want is a STOCK which is not filtered by E&O. But as explained my model is much more complex than that and for good reasons, I want the relation between E&O and CROSSTABLE going in both direction.

So the fact that I get the result that I want without understanding how power bi is thinking makes me think that I have maybe some other errors/impacts hidden somewhere in my model, and I really dont like that.... 

There are ways to filter the Crosstable even if the relationship is single direction 1:N. For example you can use RELATEDTABLE to filter the values in Crosstable if that helps...





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown  Thanks for the tip.
I can also use CROSSFILTER to change the direction on specific measure.
But this will be a monstruous work in my case, and I think that maybe if I'm able to understand how power bi is thinking, the solution will be easier.

(To be honnest, it has also become also a personnal challenge to understand it for future model I will have to do, and I also need to appease my brain 😄

Greg_Deckler
Super User
Super User

@sebastienke It is because there is no path between Calendar and MASTERDATA tables when you make the relationship single direction in the manner indicated. Since MAT is in MASTERDATA and there is no path to it from Calendar then you will get all rows.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler Yes i understand the for the distinct count of MAT, but what I dont understand is why the sum of STOCK is not filtered in both cases..... Are you able to explain this ?

Edit : Is is filtered by "month year" in both cases, but not with "mat" coming from MASTERDATA.

Edit2:  I have noticed this problem when I created this measure :

Another_way_to_calculate_stock = 
SUMX(
    SUMMARIZE(
        MASTERDATA,
        MASTERDATA[Mat],
        "@abc@",
        SUM(STOCK[Stock])
    ),
    [@abc@]
)

 
This measure will take into consideration the filter made by "mat" in Masterdata.
But in my understanding, the behavior of power bi by default when I create the table posted in my initial post, should be the same than this measure. I cannot understand why this is not the case and why the initial table doesnt take into account the "mat" filter.

@sebastienke I am unable to explain based upon the simple fact that I have no idea what the actual data looks like or the ability to test anything. Is there a code for every UPC for every month year? If so then all UPC's are in play. If all UPC's are in play then all MAT's are in play. In addition, relationships with cross filter directions of Both get complicated.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler  here is a popularization of my problem with fruits and vegetables.
If you have a look, it would be greatly appreciated.

sebastienke_0-1630287451275.png

 

sebastienke_1-1630287463607.png

 

sebastienke_2-1630287486237.png

 

sebastienke_3-1630287518447.png

 

sebastienke_4-1630287586670.png

 

sebastienke_5-1630287604656.png

NB : The column Stock is the sum of the field STOCK in the table STOCK

Hi @sebastienke ,

From your screenshot of the relationship, it looks like MASTERDATA and STOCK have a one-to-many relationship and the cross filtering direction is Single. Plese try to set the filter direction to Both and check if you can get the results you want. You can review the conent in the links below for more information on model relationships.

yingyinr_0-1630483727956.png

Cross filter direction

cross filtering

What is the Direction of Relationship in Power BI

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yiruan-msft Power BI doesn't allow me to set this relation in both directions.

My question is : 
Why E&O (which is more selective than STOCK) doesn't filter STOCK although we can see that it filters MASTERDATA.....  ? Yet, there is a relation from E&O to STOCK.
it doesn't make sense to me.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.