filter table

[powerbiexpert22]

i am trying to calculate mixed orders count however it shows same result for all years, it is happening because date table is not filtering my calculated table . i do not want to enable bidirectional relationship since it is not recommended. is there any alternate solution to show year wise results. please see below pbix file for your reference

https://drive.google.com/file/d/1XNkXKCBkl5tD60nA82ALNPEIDILJRu1b/view?usp=drive_link

[PBIViz_2024 · Accepted solution]

Hi,

Try below Dax

Mixed Orders =

    CALCULATE(DISTINCTCOUNT(orders[order_id]),orders[order_id] in values(Mixedwithoutblank[offline_order_id]))

If this works,Please do not froget to accept as solution.

Regards

[Uzi2019]

Hi @powerbiexpert22 

In some scenario you have to apply security filter in both direction. 
you can choose cross filter direction (both) anf apply security filter also.

it will resolve your issue.

Reason for security filter :

By default, row-level security filtering uses single-directional filters, regardless of whether the relationships are set to single direction or bi-directional. You can manually enable bi-directional cross-filter with row-level security by selecting the relationship and checking the Apply security filter in both directions checkbox. 

This option is only relevant if you have set row-level security in your model.
For a RLS to be effective across multiple instances of Dim table and not explicitly on the one which we created ,we have to explicitly Apply security filter in both directions.

For example, I have 2 DIM tables for Employee and Company, and 2 fact tables for EmployeeSalary and Sales.

Company and sales are 1:m relationship, Employee and EmployeeSalary are 1:1 relationship, Company and Employee are 1:m relationship

Assume that RLS is applied to the Employee table based on login.

If a bidirection filter is not applied, slicing by company is used for sales and employee salaries.
The result will be that the employee will be able to see not only his salary but also the sales of all companies
If a bidirection filter exists, then the user will only see sales for the company he is tagged in.

For more information，you can refer to：How does Row Level Security works when there is a BI-Directional Filter in Power BI/Tabular Model? 

Hope it helps,

[Uzi2019 · Accepted solution]

Hi  @powerbiexpert22 

please refer this Microsoft doc where they have not mentioned any thing about not to use bi-directional filter.

yes it is not recommended to use many to many but not birectional.

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand?source=r...

Reason of Security filter checkbox
https://learn.microsoft.com/en-us/fabric/security/service-admin-row-level-security

I hope this may clear your doubts.

[PBIViz_2024 · Accepted solution]

Hi,

Try below Dax

Mixed Orders =

    CALCULATE(DISTINCTCOUNT(orders[order_id]),orders[order_id] in values(Mixedwithoutblank[offline_order_id]))

If this works,Please do not froget to accept as solution.

Regards