Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BI_kartik
Frequent Visitor

USERELATIONSHIP complex case

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. 

BI_kartik_1-1679557194951.png

 



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

BI_kartik_2-1679557247622.png


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(
    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(
    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(
    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(
    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
BI_kartik_0-1679558731938.png

 

 

 

4 REPLIES 4
tamerj1
Super User
Super User

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?

@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

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.