DAX syntax error for ...

[ARWCL]

i have asked Power Bi to create a DAX function to show me an individuals wealth based on slicers for individual, and month end dates but when i have copied this in to Power BI Desktop I keep getting error messages saying 'The syntax for '???' is incorrect. The ??? has been [Date], [Value] etc. and i have seemed to correct these but it is not saying 'The syntax for ',' is incorrect'. I have no idea what is causing this or how to correct. Can anyone help? I know it is long but the measure is below:

Individual Wealth =

VAR SelectedMember = SELECTEDVALUE(FamilyMembers[MemberName])

-- Capture the selected date from the date slicer

-- If no date is selected, use the latest date in the Value table

VAR SelectedDate =

    IF(

        ISBLANK(SELECTEDVALUE(DateTable[Date])),

        CALCULATE(MAX('Value'[Date])),  -- Get the latest available date from the Value table

        SELECTEDVALUE(DateTable[Date])

    )

-- Calculate the most recent value for each asset owned by the selected family member up to the selected date

VAR MemberAssetValues =

    SUMX(

        FILTER(

            FamilyMembers,

            FamilyMembers[MemberName] = SelectedMember &&

            FamilyMembers[OwnershipStartDate] <= SelectedDate &&

            (FamilyMembers[OwnershipEndDate] >= SelectedDate || ISBLANK(FamilyMembers[OwnershipEndDate]))

        ),

        VAR CurrentAssetID = FamilyMembers[AssetID]

        VAR AssetValue =

            CALCULATE(

                MAX('Value'[Value]),

                'Value'[AssetID] = CurrentAssetID,

                'Value'[Date] <= SelectedDate  -- Consider the most recent value prior to or equal to the selected date

            )

        -- Handle the ownership of other assets, e.g., AssetID 1 owns AssetID 3

        VAR SubAssetValue =

            SUMX(

                FILTER(

                    Value,

                    Value[ParentAssetID] = CurrentAssetID &&

                    Value[Date] <= SelectedDate

                ),

                Value[Value] *

                LOOKUPVALUE(

                    FamilyMembers[OwnershipPercentage],

                    FamilyMembers[AssetID], Value[ParentAssetID],

                    FamilyMembers[MemberName], SelectedMember

                )

            )

        -- Calculate the total value for the current asset including its ownership of other assets

        RETURN

            (AssetValue + SubAssetValue) * FamilyMembers[OwnershipPercentage]

    )

-- Case when no individual is selected, calculate the total wealth for all family members

VAR TotalWealthAllMembers =

    SUMX(

        FILTER(

            FamilyMembers,

            FamilyMembers[OwnershipStartDate] <= SelectedDate &&

            (FamilyMembers[OwnershipEndDate] >= SelectedDate || ISBLANK(FamilyMembers[OwnershipEndDate]))

        ),

        VAR CurrentAssetID = FamilyMembers[AssetID]

        VAR AssetValue =

            CALCULATE(

                MAX(Value[Value]),

                Value[AssetID] = CurrentAssetID,

                Value[Date] <= SelectedDate

            )

        -- Handle sub-assets owned by this asset (e.g., AssetID 1 owning AssetID 3)

        VAR SubAssetValue =

            SUMX(

                FILTER(

                    Value,

                    Value[ParentAssetID] = CurrentAssetID &&

                    Value[Date] <= SelectedDate

                ),

                Value[Value] *

                LOOKUPVALUE(

                    FamilyMembers[OwnershipPercentage],

                    FamilyMembers[AssetID], Value[ParentAssetID]

                )

            )

        -- Calculate total value of the asset including sub-assets

        RETURN

            (AssetValue + SubAssetValue) * FamilyMembers[OwnershipPercentage]

    )

-- Final calculation: if a member is selected, calculate their wealth; if not, show the total wealth

RETURN

IF(

    ISBLANK(SelectedMember),

    TotalWealthAllMembers,  -- No selection, show total wealth

    MemberAssetValues  -- Member selected, show individual wealth based on the date

)

[Anonymous · Accepted solution]

Hi @ARWCL 

Based on your data and the final goal, i create the following measures.

Note:there are no relationships among tables.

MEASURE =
VAR filterdate =
    IF (
        ISFILTERED ( DateTable[Date] ),
        MAX ( DateTable[Date] ),
        MAXX ( ALLSELECTED ( 'Value'[Date] ), [Date] )
    )
VAR filterassetid =
    CALCULATETABLE (
        VALUES ( 'FamilyMembers'[AssetID] ),
        FamilyMembers[OwnershipStartDate] <= filterdate,
        OR (
            FamilyMembers[OwnerShipEndDate] >= filterdate,
            ISBLANK ( FamilyMembers[OwnerShipEndDate] )
        )
    )
VAR maxdate =
    MAXX (
        FILTER (
            ALLSELECTED ( 'Value' ),
            [AssetID]
                IN filterassetid
                    && [AssetID]
                        IN VALUES ( 'Value'[AssetID] )
                            && [Date] <= filterdate
        ),
        [Date]
    )
VAR maxvalue =
    MAXX (
        FILTER (
            ALLSELECTED ( 'Value' ),
            [Date] = maxdate
                && [AssetID]
                    IN VALUES ( 'Value'[AssetID] )
                        && [AssetID] IN filterassetid
        ),
        [Value]
    )
VAR subvalue =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Value' ),
            [ParentAssetID]
                IN VALUES ( 'Value'[AssetID] )
                    && [ParentAssetID]
                    IN filterassetid
                        && [Date] <= filterdate
        ),
        [Value]
    )
VAR ownerper =
    MAXX (
        FILTER (
            ALLSELECTED ( FamilyMembers ),
            [AssetID]
                IN VALUES ( FamilyMembers[AssetID] )
                    && [AssetID]
                        IN VALUES ( 'Value'[AssetID] )
                            && [AssetID] IN filterassetid
        ),
        [OwnershipPercentage]
    )
RETURN
    ( maxvalue + subvalue * ownerper ) * ownerper

Measure2 =
VAR a =
    ADDCOLUMNS (
        SUMMARIZE ( ALLSELECTED ( 'Value' ), [AssetID] ),
        "Measure", [Measure]
    )
RETURN
    SUMX ( FILTER ( a, [AssetID] IN VALUES ( 'Value'[AssetID] ) ), [Measure] )

Then put the measure2 to the card.

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@ARWCL very hard to tell from DAX expressions. It will be easier if you share pbix file using one drive/google drive with the expected output. Remove any sensitive information before sharing.

[ARWCL]

See below images of the test tables and Power BI report i have tried to create. Should be really easy to replicate and test. Thank you