Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
)
)
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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |