Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX Measure Help

Hi Experts, I would like to create 3 DAX measure for below content. Any one please help.   1) AUM_MAR : Sum of market_value_usd per Customer_No for latest BusinessDate 2) Bal_Fun : If Fin_Level2=...
Need Help
Show and Tell
  • V-lianl-msft's avatar
    V-lianl-msft
    4 years ago

    Please try measures like below:

    AUM_MAR =
CALCULATE (
    SUM ( 'Table'[market_value_usd] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Customer_No] ),
        'Table'[BusinessDate ] = MAX ( 'Table'[BusinessDate ] )
    )
)
    Bal_Fun =
VAR current_cus =
    SELECTEDVALUE ( 'Table'[Customer_No] )
RETURN
    CALCULATE (
        SUM ( 'Table'[market_value_usd] ),
        FILTER (
            'Table',
            'Table'[Customer_No] = current_cus
                && 'Table'[BusinessDate ] = MAX ( 'Table'[BusinessDate ] )
                && 'Table'[Fin_Level2] = "Funds"
        )
    )
    Bal_Dep =
VAR current_cus =
    SELECTEDVALUE ( 'Table'[Customer_No] )
RETURN
    CALCULATE (
        SUM ( 'Table'[market_value_usd] ),
        FILTER (
            'Table',
            'Table'[Customer_No] = current_cus
                && 'Table'[BusinessDate ] = MAX ( 'Table'[BusinessDate ] )
                && 'Table'[Fin_Level2] = "Deposits and Current Account"
        )
    )

    If the problem persists, please provide dummy data based on your data model.https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216 

V-lianl-msft's avatar
V-lianl-msft
Community Support
4 years ago

Please try measures like below:

AUM_MAR =
CALCULATE (
    SUM ( 'Table'[market_value_usd] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Customer_No] ),
        'Table'[BusinessDate ] = MAX ( 'Table'[BusinessDate ] )
    )
)
Bal_Fun =
VAR current_cus =
    SELECTEDVALUE ( 'Table'[Customer_No] )
RETURN
    CALCULATE (
        SUM ( 'Table'[market_value_usd] ),
        FILTER (
            'Table',
            'Table'[Customer_No] = current_cus
                && 'Table'[BusinessDate ] = MAX ( 'Table'[BusinessDate ] )
                && 'Table'[Fin_Level2] = "Funds"
        )
    )
Bal_Dep =
VAR current_cus =
    SELECTEDVALUE ( 'Table'[Customer_No] )
RETURN
    CALCULATE (
        SUM ( 'Table'[market_value_usd] ),
        FILTER (
            'Table',
            'Table'[Customer_No] = current_cus
                && 'Table'[BusinessDate ] = MAX ( 'Table'[BusinessDate ] )
                && 'Table'[Fin_Level2] = "Deposits and Current Account"
        )
    )

If the problem persists, please provide dummy data based on your data model.https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216