DISTINCTCOUNT with multiple USERELATIONSHIPS

[blairc15]

Hello,

I have two tables: 1) A table (Universe Table) listing all possible stores that we could sell a product into 2) A table (Depletion Data Table) listing all stores that product was actually sold into.

I'm trying to create a measure to show the percentage of stores that we have sold product into over certain dimensions (dates, product type, location, etc.)

I've created a relationship between these two tables based on the field TDLINX_OULET_CODE. The problem is there are more outlet codes/fields in the Universe Table vs the Depletion Table.

When I cross filter both it removes all stores without a match in the Depletion Data Table from the Universe Table and shows too high of a distribution %. And when I conduct a Distinctcount it doesn't filter on the dimensions from the Depletion Data Table (Product, Location, etc.) showing too little of a distribution %. Finally, I tried to use USERELATIONSHIP, which works but only for one dimension at a time.

Is there a way to use a dynamic measure that will change with the filters applied in the Depletion Data table to the Universe Data Table. For example, if I select the dimensions, State = AK, Trade Channel = Liquor, this would show the corresponding stores in the Universe Table and thus distribution %?

[v-yueyunzh-msft]

Hi , @blairc15 

Based on your description, you want to automatically modify the relationship between two tables. Right?

Currently in Power BI, USERELATIONSHIP() can only be used in functions that take a filter as an argument.

And there is an activation relationship between the two tables, and if the USERELATIONSHIP() function is not used, the current filtering context is calculated in the activation relationship, we can use the USERELATIONSHIP() function in the measure to activate the inactive relationship, and only the measure is calculated according to the relationship, if we want to implement the calculation in multiple dimensions, we need to create multiple measures to achieve.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[blairc15]

Hi Aniya, thank you for your response. It is not feasible to have different measures. Is there another way to achieve the same result without having to use "USERELATIONSHIPS"?