topic Re: Creating Measure for a table without using USERELATIONSHIP() in Developer

Creating Measure for a table without using USERELATIONSHIP()

dsp9911 — Wed, 12 Oct 2022 17:25:21 GMT

I have 2 tables one is geography table(dimension table) and another one is a fact table.In the fact table the seller gets order from all over the world and so there are 2 columns where ordered country show from which country the order is placed and warehouse country shows from which country the order will be shipped.

I have to count how many orders were placed from a country for business requirements and also which country is handling the most number of orders.I have created a active and an inactive relationship between the 2 tables.

1. geography(country) ---> fact(ordered country)

2.geography(country) ---> fact(warehouse country)

I have created a measure in fact table to count which country is handling most number of orders.

count of warehouse country = CALCULATE(COUNT(List[Warehouse Country]),USERELATIONSHIP(geography[country],LIST[warehouse country]))

So this gives me data for each country like which country handles how much orders and this works fine.

But when i apply RLS on Country from geography table it gives a error where I cant use the USERELATIONSHIP() with RLS.

I need both the relationship in my model for calculations.

So is there any workaround for this?(also i cant create a duplicate geography table to have seperate relationship on different data due to size restrictions)

@Anonymous @Sus @PaulDBrown @Greg_Deckler

Re: Creating Measure for a table without using USERELATIONSHIP()

PaulDBrown — Wed, 12 Oct 2022 18:23:50 GMT

Not sure if RLS allows the use of TREATAS, but you can try:

Warehouse count = CALCULATE ( [Count warehouse Country], ALL ( 'Geagraphy Table' ), TREATAS ( VALUES ( 'Geagraphy Table'[Country] ), 'List Table'[Warehouse Country] ) )

Re: Creating Measure for a table without using USERELATIONSHIP()

dsp9911 — Thu, 13 Oct 2022 04:49:50 GMT

I used TREATAS but that leads to loss of some data i.e if value for a seller should be 500 after using TREATAS it gives only 430

Re: Creating Measure for a table without using USERELATIONSHIP()

PaulDBrown — Thu, 13 Oct 2022 06:28:33 GMT

Any chance you can share some sample data?

Re: Creating Measure for a table without using USERELATIONSHIP()

dsp9911 — Thu, 13 Oct 2022 09:12:27 GMT

Sorry but can't share the data.

Re: Creating Measure for a table without using USERELATIONSHIP()

PaulDBrown — Fri, 14 Oct 2022 09:22:09 GMT

I've tested this and I think all you need is a simple COUNT measure...

Count Warehouse = COUNT('List Table'[Warehouse Country])

Re: Creating Measure for a table without using USERELATIONSHIP()

dsp9911 — Fri, 14 Oct 2022 14:49:31 GMT

It doesnt work when RLS is applied on Gcountry column the visualisation using measure count warehouse breaks and doesn't show data

Re: Creating Measure for a table without using USERELATIONSHIP()

mariussve1 — Sun, 16 Oct 2022 21:54:20 GMT

Hi,

Does this help you?

https://community.powerbi.com/t5/Desktop/USERELATIONSHIP-and-RLS/m-p/787463

Marius