topic Re: USERELATIONSHIP complex case in DAX Commands and Tips

USERELATIONSHIP complex case

BI_kartik — Thu, 23 Mar 2023 08:05:57 GMT

Hi,
I have a following business problem that I need your help solving.
I have two Dimension tables - Account Type DIM and Volume Type DIM
and One FACT table - Volume FACT (This is just a part of my complex data model)
The relationships from DIM to FACT are one to many and all are inactive relationships (I know I can keep one active relationship but I have delibrately kept it inactive). I have marked all the relationships columns in the below screen shot.

I want to calculate Total volume which can be done by summing the following columns =
VOLUME FACT[manual_opened_usd_volume] + VOLUME FACT[manual_closed_usd_volume] + VOLUME FACT[copytrading_opened_usd_volume] + VOLUME FACT[copytrading_closed_usd_volume]

Now In the report view I have the following slicers

I want the Total Volume to change when I interact with the slicers.
Let me share the DAX Expression that I wrote.

Total Volume_new =

VAR __Manual_open_volume =

CALCULATE(

SUM(

'VOLUME FACT'[manual_opened_usd_volume]

USERELATIONSHIP(

'VOLUME FACT'[Manual Open Volume Index],

'Volume Type DIM'[Index]

)

USERELATIONSHIP(

'VOLUME FACT'[Provider Index],

'Account Type DIM'[id]

USERELATIONSHIP(

'VOLUME FACT'[Follower Index],

'Account Type DIM'[id]

)

VAR __Manual_close_volume =

CALCULATE(

SUM(

'VOLUME FACT'[manual_closed_usd_volume]

USERELATIONSHIP(

'VOLUME FACT'[Manual Close Volume Index],

'Volume Type DIM'[Index]

)

USERELATIONSHIP(

'VOLUME FACT'[Provider Index],

'Account Type DIM'[id]

USERELATIONSHIP(

'VOLUME FACT'[Follower Index],

'Account Type DIM'[id]

)

VAR __Copytrading_open_volume =

CALCULATE(

SUM(

'VOLUME FACT'[copytrading_opened_usd_volume]

USERELATIONSHIP(

'VOLUME FACT'[Copy Trading Open Volume Index],

'Volume Type DIM'[Index]

)

USERELATIONSHIP(

'VOLUME FACT'[Provider Index],

'Account Type DIM'[id]

USERELATIONSHIP(

'VOLUME FACT'[Follower Index],

'Account Type DIM'[id]

)

VAR __Copytrading_close_volume =

CALCULATE(

SUM(

'VOLUME FACT'[copytrading_closed_usd_volume]

USERELATIONSHIP(

'VOLUME FACT'[Copytrading Close Volume Index],

'Volume Type DIM'[Index]

)

USERELATIONSHIP(

'VOLUME FACT'[Provider Index],

'Account Type DIM'[id]

USERELATIONSHIP(

'VOLUME FACT'[Follower Index],

'Account Type DIM'[id]

)

RETURN

__Manual_open_volume

__Manual_close_volume

__Copytrading_open_volume

__Copytrading_close_volume

The Result is not filtering the data as it should and giving the following error when i select provider / follower from account_type slicer

Re: USERELATIONSHIP complex case

tamerj1 — Thu, 23 Mar 2023 08:44:56 GMT

Hi BI_kartik
Please try

Total Volume_new = SUMX ( VALUES ( 'Account Type DIM'[id] ), VAR CurrentAccount = 'Account Type DIM'[id] RETURN CALCULATE ( VAR __Manual_open_volume = CALCULATE ( SUM ( 'VOLUME FACT'[manual_opened_usd_volume] ), USERELATIONSHIP ( 'VOLUME FACT'[Manual Open Volume Index], 'Volume Type DIM'[Index] ) ) VAR __Manual_close_volume = CALCULATE ( SUM ( 'VOLUME FACT'[manual_closed_usd_volume] ), USERELATIONSHIP ( 'VOLUME FACT'[Manual Close Volume Index], 'Volume Type DIM'[Index] ) ) VAR __Copytrading_open_volume = CALCULATE ( SUM ( 'VOLUME FACT'[copytrading_opened_usd_volume] ), USERELATIONSHIP ( 'VOLUME FACT'[Copy Trading Open Volume Index], 'Volume Type DIM'[Index] ) ) VAR __Copytrading_close_volume = CALCULATE ( SUM ( 'VOLUME FACT'[copytrading_closed_usd_volume] ), USERELATIONSHIP ( 'VOLUME FACT'[Copytrading Close Volume Index], 'Volume Type DIM'[Index] ) ) RETURN __Manual_open_volume + __Manual_close_volume + __Copytrading_open_volume + __Copytrading_close_volume, 'VOLUME FACT'[Provider Index] = CurrentAccount, 'VOLUME FACT'[Follower Index] = CurrentAccount ) )

Re: USERELATIONSHIP complex case

BI_kartik — Thu, 23 Mar 2023 10:44:12 GMT

Thank you for your response tamerj1.
But your solution is not working. Im getting blank if I select between provider and follower. Also, with no filter applied, the total volume is not correct.

Re: USERELATIONSHIP complex case

tamerj1 — Thu, 23 Mar 2023 11:06:33 GMT

BI_kartik
This is the logic that I understood from your DAX code. I believe this is the condition that generates blanks everywhere

'VOLUME FACT'[Provider Index] = CurrentAccount, 'VOLUME FACT'[Follower Index] = CurrentAccount

What did you mean by activating the two relationships between 'VOLUMN FACT' and 'Account Type DIM' together?

Re: USERELATIONSHIP complex case

BI_kartik — Fri, 24 Mar 2023 12:13:57 GMT

tamerj1

What I want to do here is that,
CASE 1 : when I select "provider" from account type filter the volume should show
total volume = manual open + manual close + copytrading open + copytrading close (only for providers)
similarly for when i select follower.

CASE 2 : Also, when i have selected provider from account type filter and manual open volume from volume type filter the total volume should be = manual open (only for provider)

CASE 3 : When i select only manual open from the volume type filter,
the total volume should be = manual open ( for both provider and followers).

I know where the problem is in my code, its just that I don't know how to solve this. let me explain it using a part of the code that I have written (manual open variable)

VAR __Manual_open_volume =
CALCULATE(
  CALCULATE(
  SUM(
  'VOLUME FACT'[manual_opened_usd_volume]
),
  USERELATIONSHIP(
  'VOLUME FACT'[Manual Open Volume Index],
  'Volume Type DIM'[Index]
)
),
  USERELATIONSHIP(
  'VOLUME FACT'[Provider Index],
  'Account Type DIM'[id]
),
  USERELATIONSHIP(
  'VOLUME FACT'[Follower Index],
  'Account Type DIM'[id]
)
)

Now, in CASE 1 : When I have provider selected, The above DAX expression does not know what to compute as both these userelationships are under one calculate.
USERELATIONSHIP(
  'VOLUME FACT'[Provider Index],
  'Account Type DIM'[id]
),
  USERELATIONSHIP(
  'VOLUME FACT'[Follower Index],
  'Account Type DIM'[id]
)

Hence, the error.

If for example, I just remove the USERELATIONSHIP code for follower from the code. The calcualtion will be okay for anything selected from volume type filter and only provider is selected from account type filter.

  USERELATIONSHIP(
  'VOLUME FACT'[Follower Index],
  'Account Type DIM'[id]
)

The key is to make DAX understand that when provider is selected it needs to compute the following
VAR __Manual_open_volume =
CALCULATE(
  CALCULATE(
  SUM(
  'VOLUME FACT'[manual_opened_usd_volume]
),
  USERELATIONSHIP(
  'VOLUME FACT'[Manual Open Volume Index],
  'Volume Type DIM'[Index]
)
),
  USERELATIONSHIP(
  'VOLUME FACT'[Provider Index],
  'Account Type DIM'[id]
)
)

and when follower is selected DAX needs to compute the following
VAR __Manual_open_volume =
CALCULATE(
CALCULATE(
SUM(
'VOLUME FACT'[manual_opened_usd_volume]
),
USERELATIONSHIP(
'VOLUME FACT'[Manual Open Volume Index],
'Volume Type DIM'[Index]
)
),
USERELATIONSHIP(
'VOLUME FACT'[Follower Index],
'Account Type DIM'[id]
)
)

Its like "IF OR" statement between the two USEREALTIONSIHPS,
If provider then compute manual open volume for provider and if follower then compute manual open volume for follower