Creating Measure for a table without using USERELATIONSHIP()

[dsp9911]

Hi 

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 

[mariussve1]

Hi,

Does this help you?

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

Br

Marius

[PaulDBrown]

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

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

[dsp9911]

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

[PaulDBrown]

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]
    )
)

[dsp9911]

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 

[PaulDBrown]

Any chance you can share some sample data?

[dsp9911]

Sorry but can't share the data.