Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
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 :
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 :
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.
Solved! Go to Solution.
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).
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).
no one in the community has the answer ? 😕
@Greg_Deckler here is a popularization of my problem with fruits and vegetables.
If you have a look, it would be greatly appreciated.
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....
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:
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.
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).
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
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 ?
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]
)
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 ?
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...
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 😄 )
@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.
@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.
@Greg_Deckler here is a popularization of my problem with fruits and vegetables.
If you have a look, it would be greatly appreciated.
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.
What is the Direction of Relationship in Power BI
Best Regards
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |