topic Re: USERELATIONSHIP when certain value is selected in DAX Commands and Tips

USERELATIONSHIP when certain value is selected

Anonymous — Thu, 04 Nov 2021 16:40:21 GMT

Hi,

I'm filtering table Person ID with the Field of business dimensional table. If one specific field (for example Field 1) is selected we want to filter every person ID on every company. But in case the selection is "Similar" we should only get a list of person ID's who belong to any company that is similar for the selected company.

I wonder if there is a way to build a measure that activates the inactive relation between Company 2 and Person ID whenever "Similar" is selected.

Thanks in advance!

Julia

Re: USERELATIONSHIP when certain value is selected

mahoneypat — Fri, 05 Nov 2021 00:18:58 GMT

You could write a measure with two variables - one with USERELATIONSHIP and one without, and then use IF with SELECTEDVALUE on your slicer column to decide which to return.

Pat

Re: USERELATIONSHIP when certain value is selected

Anonymous — Fri, 05 Nov 2021 12:06:23 GMT

Thanks, your solution moved me forward but I still need some additional guidance. This is how my measure worked out:

Measure =
var _similar = CALCULATE(DISTINCTCOUNT('PERSON ID'[id]), USERELATIONSHIP(PERSON ID[id], Company2[Id]))
var _all = CALCULATE(DISTINCTCOUNT('PERSON ID[id])

RETURN

CALCULATE(IF(SELECTEDVALUE(Field of Business[key]) = "Similar", _similar, _all))

The problem is, I only get the calculated amount of similar id's but it is not possible to use them as row values. In the Person ID table I have additional information, such as gender. I'd like to visualize how id's within each and every selection are divided into men and women.

When I select any other field than "Similar", the column chart works perfectly. When selecting "Similar", the visualization is turning all white.

Do you have an idea how this could be fixed?

Re: USERELATIONSHIP when certain value is selected

Anonymous — Mon, 08 Nov 2021 14:59:32 GMT

Does anybody have an idea how to fix this? 🙂

Re: USERELATIONSHIP when certain value is selected

v-yanjiang-msft — Tue, 09 Nov 2021 06:01:26 GMT

Hi Anonymous ,

According to your description, I create a sample, the measure is just like you wrote.

When I drag it into the values, and select “similar”, it can work fine like other fields.

Here’s my sample bellow.

Best Regards,
Community Support Team _ kalyj

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

Re: USERELATIONSHIP when certain value is selected

Anonymous — Tue, 09 Nov 2021 16:14:41 GMT

Thanks v-yanjiang-msft! Your answer made me realize my model wasn't following exactly the picture attached. After making some relation changes the report started to work as I had hoped. Thanks for making such a huge effort 🙂