Using CROSSFILTER having issue in RLS

[gurudayalsahu]

Hi,

I am getting the belw error after enabling RLS :

Couldn’t load the data for this visual : Join paths are expected to form a tree, but the table ‘location_hirearchy01’ has two join paths to table ‘subcenter’: ‘location_hirearchy01->’subcenter’ and ‘location_hirearchy01'->’subcenter’->’identifiation’->’subcenter’.

1. ) The below Tables has Cross Filtered relationship

’followup’->’subcenter’, 

2.)  The below Tables has got Many to one relationship with both cross filter direction.

‘location_hirearchy01 (one) ->’subcenter’ (many)

It worked perfectally fine but after enablign the RLS, we started facing the issue.

blockCount = CALCULATE(DISTINCTCOUNT(location_hirearchy01[block_id]),CROSSFILTER(subcenter[asha_case_id],identifiation[asha_case_id],Both))

Our requirement to count the Block_id from the Table "location_hirearchy0".

So, what shoudl be the alternate option to be used instead of CROSSFILTER funcation.

Thanks in advance.

GDS

[parry2k]

@gurudayalsahu thanks for providing more details but unfortunately it is not very helpful, you are telling how these tables are related but not telling which table is on one side and which table is on many side. In relationship there are two parts, how tables are connected, 1 to 1, 1 to many, etc and when it comes to one to many, it is important to know which table is on many side or which table is on one side (one of these)

You are overcomplicating everything, it will be easier if you share a sample pbix file with the expected output, I'm sure genius here on the forum will get your solution pretty quickly and easily but you need to explain your issue in a better way.

[gurudayalsahu]

Thanks so much for quick reply.

It seems some issues or confusion  in Data Modelling due to the Cross Filtering, so i removed the measure having CrossFiltering.

The latest Model structure as follows :

Table#1 : subcenter          

Table#2 : location_hirearchy01 

The above Tables are having the below relationship (Many to one with Cross Filter direction as Both) and connected through the field named "Subcenter_Code"

Table#3 : identifiation

Table#1 : subcenter  

The above Tables (Identification and Subcenter) are having the below relationship (Many to one and cross filter direction as Single) and connected through the field named "asha_id"

Our requirement to create a measure for the distinct count of "Block_id" from "location_hirearchy01" table by filtering the "form_id" of  "identifiation" Table.

Block_Count = CALCULATE(DISTINCTCOUNT(location_hirearchy01[block_id]),FILTER(indentification,

COUNT(indentification[form_id]>0))

But its not getting the correct number of counting.

I used previuolsy the below Measure but due to RLS, we got it removed :

CALCULATE(DISTINCTCOUNT(location_hirearchy01[block_id]),CROSSFILTER(subcenter[asha_case_id],

indentification[asha_case_id],Both))

So could you please suggest me the proper syntax for the block_count for the unique count of the Block_id

from "location_hirearchy01", as we dont want to use the Crossfiltering.

Thanks once again in advance!

Best,

GDS

[parry2k]

@gurudayalsahu not sure what you mean by crossfilter relationship, are you referring to crossfilter to both, if that is the case why in the measure you are using crossfilter since it is already both direction.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡