Forum Discussion
USERELATIONSHIP complex case
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.
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?
- BI_kartik3 years agoFrequent Visitor
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