Forum Discussion

ARWCL's avatar
ARWCL
Regular Visitor
1 year ago
Solved

DAX syntax error for ...

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
)
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

     

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    1 year ago

    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's avatar
      ARWCL
      Regular Visitor
      1 year ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.