Inactive relationship leading to wrong result in measure

[AmiraBedh]

I have the following model : 

My following measure works fine as it only implicates the Fact Settlements and Dim Settlements :

TotalSettlementsAmount =

CALCULATE(

    SUMX(

        'Fact Settlements',

        IF(

            (

                RELATED('Dim Settlement'[Settlement Code Type]) = "D" ||

                (

                    RELATED('Dim Settlement'[Settlement Code Type]) = "B" &&

                    'Fact Settlements'[Settlements Amount] > 0

                )

            ) &&

            LEFT(RELATED('Dim Settlement'[Settlement Group]), 1) = "V" &&

            LEFT(RELATED('Dim Settlement'[Settlement Id]), 1) <> "A",

            'Fact Settlements'[Settlements Amount],

            0

        )

    )


)

 The measure follows this T-SQL query and it is giving correct result :

SELECT

    SUM(

        CASE

            WHEN DS.[Settlement Code Type] ='D' OR (DS.[Settlement Code Type]='B'

                                           AND FS.[Settlements Amount] > 0) THEN FS.[Settlements Amount]

                                          

                                          

            ELSE 0

        END

    ) AS TotalSettlementsAmount

FROM

    [Fact Settlements] AS FS

    LEFT JOIN [Dim Settlement] AS DS ON DS.[Settlement Id] = FS.[Settlements Settlement Id]

    

WHERE

    DS.[Settlement Group] LIKE 'V%'

    AND  DS.[Settlement Id] NOT LIKE 'A%'

Now comes the part when I need to add filters based on Client Payment Invoice Number which exists in Dim Client Payment.

You can notice logically the path from Fact Settlements to Dim Client Payment should be like below if I follow the joins in T-SQL :

Fact Settlements > Dim Client > Fact Client Payments >Dim Client Payments

But in Power BI the relationship between the Fact Settlements and Dim Client is inactive so it will be passing through Dim Account and it is giving wrong results.

This is my measure when I include the filters based on Client Payment Invoice Number which exists in Dim Client Payment :

TotalSettlementsAmount =

CALCULATE(

    SUMX(

        'Fact Settlements',

        IF(

            (

                RELATED('Dim Settlement'[Settlement Code Type]) = "D" ||

                (

                    RELATED('Dim Settlement'[Settlement Code Type]) = "B" &&

                    'Fact Settlements'[Settlements Amount] > 0

                )

            ) &&

            LEFT(RELATED('Dim Settlement'[Settlement Group]), 1) = "V" &&

            LEFT(RELATED('Dim Settlement'[Settlement Id]), 1) <> "A",

            'Fact Settlements'[Settlements Amount],

            0

        )

    )

 

    , FILTER (

            'Dim Client Payment',

            LEFT('Dim Client Payment'[Client Payment Invoice Number], 2) <> "VF"

            && LEFT('Dim Client Payment'[Client Payment Invoice Number], 1) = "V"

        )

)

and the T-SQL query behind : 

SELECT

 

    SUM(

        CASE

            WHEN DS.[Settlement Code Type] ='D' OR (DS.[Settlement Code Type]='B'

                                           AND FS.[Settlements Amount] > 0) THEN FS.[Settlements Amount]

                                          

                                          

            ELSE 0

        END

    ) AS TotalSettlementsAmount

FROM

    [Fact Settlements] AS FS

    LEFT JOIN [Dim Settlement] AS DS ON DS.[Settlement Id] = FS.[Settlements Settlement Id]

   LEFT JOIN [collections].[Dim Client] AS DC ON DC.[Client Id] = FS.[Settlements Client Id]

    LEFT JOIN [Fact Client Payments] AS FCP ON FCP.[Client Payments Client Id] = DC.[Client Id]

    LEFT JOIN [Dim Client Payment] AS DCP ON DCP.[Client Payment Id] = FCP.[Client Payments Payment Id]


WHERE

    DS.[Settlement Group] LIKE 'V%'

    AND  DS.[Settlement Id] NOT LIKE 'A%'

AND DCP.[Client Payment Invoice Number] LIKE 'V%'

AND DCP.[Client Payment Invoice Number] NOT LIKE 'VF%'

How should I proceed in this case ?

[AmiraBedh · Accepted solution]

I would go for changing my model to star schéma. Thank you for your remarks.

[lbendlin]

Measures do not use RELATED. They use the data model.  Calculated Columns use RELATED.

[AmiraBedh]

I totally agree with you. It was just an attempt due to the complexity of the model.

Just to leave an explanation why measures do not use RELATED :

In more complex data models, especially those with indirect relationships or situations where context transition occurs (when using CALCULATE), users might incorrectly think that RELATED is necessary to pull in related data. However, properly constructed measures generally navigate relationships correctly without needing RELATED.

[sevenhills]

l lost the context on what is getting wrong after reading twice your post. Sorry!

https://medium.com/@kalaranibe/understanding-related-relatedtable-and-userelationship-in-dax-f867d8b...

All I can say is that you have to use "USERELATIONSHIP" along with RELATED. Please check the links and see if it is useful.

Recap:

https://learn.microsoft.com/en-us/dax/related-function-dax

https://learn.microsoft.com/en-us/dax/userelationship-function-dax