Forum Discussion
Cardinality and filtering
Hi Guys,
I am new to power bi and would like to know that How my left table is filtering my right table as there is no direct relationship?
Report View-
Hi Anonymous ,
This is my test data.
Left table:
Right table:
Fact table:
Relational model:
Filter from one end to multiple ends. That is, the left table id filters the fact table ID.
The overlapping ID is [1,2,3,4]. Then observe that the sales values with id [1, 2, 3, 4] and the corresponding id [1, 2, 3, 4] in the right table are [15, 17, 19, null] respectively.
The right table id filters the fact table ID. The overlapping part has an ID of [1,2,3,6], and the sales value with id [1,2,3,6] in the right table is [15,17,19,23].
At this point we can get. Since the left table does not have id[6], it is null corresponding to the sales value[23].
Finally, the union of picture① and picture② results is taken.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
- Greg_Deckler
Community Champion
NitishChaubey What is the aggregation or measure formula for your Sales from right table?
Hi Greg_Deckler ,Upon digging it further i came came to know that I have made "Dont Summarize" option selected on all the sales field of my left,right and fact table.
I also got know that my left table is not filtering my right table directly.
But I would like to know what kind of join operations are happening at the backend in the result table.How the data is being displayed on the result table here?Is it a simple inner join happening or something else?
Hi NitishChaubey ,
You can use the TREATAS() function to simulate a relationship when no relationship exists.
Your syntax would be something along the lines of
CALCULATE( [Measure], TREATAS(Incident[RequesterID], Problem[Employee???}) )You also can use the query editor to create a merged table as a slicer, and create if measure as a visual level filter.
Please refer to the solution for similar posts:
Solved: Filtering table 2 without relationship - Microsoft Power BI Community
Solved: Dynamically filter one table by another without us... - Microsoft Power BI Community
Solved: Look up filtered value without relationship - Microsoft Power BI Community
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
Because you have fields from different tables in your visual, filtering works. This is by design. If you want to test whether the left table can filter the right table, you can put all the fields of the right table into a visual and the left table fields into a slicer to check if they can interact. Depending on the document, filter by arrow direction, or you can set up a bidirectional relationship for two-way filtering.
Please refer to the following document for more information.
Model relationships in Power BI Desktop - Power BI | Microsoft Learn
Many-to-many relationship guidance - Power BI | Microsoft Learn
Bi-directional relationship guidance - Power BI | Microsoft Learn
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
