Calculate a measure with a filter that doesn't propagate to table

[HelloPBIWorld]

The relationships in my model are like below :

• d_collection_types[Collection type id] is 1 to many to f_collections[Collection_Type_id] with filter direction (unidirection) from d_collection_types to f_collections
• Dim Account[Account Id] is 1 to many Fact Ledger[Ledger Account Id] with filter direction (unidirection) from Dim Account to Fact Ledger
• d_balance_mappings[Balance ID] is 1 to many to Fact Ledger[Ledger Balance Id] bidirection from d_balance_mappings to Fact Ledger
• Dim Account[Client Id] is many to 1 to Dim Client[Client Id] with filter direction (unidirection) from Dim Client to Dim Account
• Dim Account[Account Id] is 1 to many to f_account_statuses[Account Id] with filter direction (unidirection) from Dim Account to f_account_statuses
• d_accounts_payment_status[Account payment status id] is 1 to many to f_account_statuses[Account payment status id] with filter direction (unidirection) from d_accounts_payment_status to f_account_statuses

I want  to create a measure Total Ledger Balance Amicable as below but you can see that balance type doesn't filter the fact ledger because of the complexity of the model and the way the relationships are implemented.

The logic behind the measure is :

SUM('Fact Ledger'[Ledger Balance]) where 'd_balance_mappings'[Balance Type] = "Balance" or "Costs"

minus
SUM('Fact Ledger'[Ledger Balance]) where 'd_balance_mappings'[Balance Type] = "Payments"
the result should be for [Collection type] = 'Amicable'

How can I achieve this ?

[v-tsaipranay]

Hi @HelloPBIWorld ,

Thank you for reaching out to the Microsoft fabric community forum.

Could you please confirm if the issue has been resolved. I wanted to check if you had the opportunity to review the information provided by @rohit1991 . Please feel free to contact us if you have any further questions.

Thank you.

[rohit1991]

Hi @HelloPBIWorld 

Since the relationships don’t allow filters to flow directly to the Fact Ledger (especially from d_balance_mappings), we can solve this using explicit filters inside the measure.

Measure:

daxCopyEditTotal Ledger Balance (Amicable) = 
VAR BalancePart =
    CALCULATE(
        SUM('f_fact_ledger'[Ledger Balance]),
        'd_balance_mappings'[Balance Type] IN {"Balance", "Costs"},
        'd_collection_types'[Collection type] = "Amicable"
    )
VAR PaymentPart =
    CALCULATE(
        SUM('f_fact_ledger'[Ledger Balance]),
        'd_balance_mappings'[Balance Type] = "Payments",
        'd_collection_types'[Collection type] = "Amicable"
    )
RETURN
    BalancePart - PaymentPart

[HelloPBIWorld]

The  'd_collection_types'[Collection type] = "Amicable" is not filtering the measure even when I put it.

If you check the relationships I mentioned you can see that Dim Account is in between the 2 fact tables

[rohit1991 · Accepted solution]

Hi @HelloPBIWorld 

Option A (model fix – best for long term)

Add the Collection_Type_id to the fact (via PQ merge) or create a proper dim that’s on a single filter path to Fact Ledger:
d_collection_types >> f_collections >> Fact Ledger with filters flowing downstream. Then your original measure will work without tricks.

Option B (keep model – enforce filter in DAX with TREATAS)

Push the selected collection IDs onto the fact using TREATAS:

Total Ledger Balance (by Type) =
VAR SelectedCollectionIDs =
   CALCULATETABLE(
       VALUES ( 'f_collections'[Collection_ID] ),
       // use this if you want slicer-driven type
       KEEPFILTERS ( VALUES ( 'd_collection_types'[Collection type] ) )
       // or hard-code to Amicable:
       // 'd_collection_types'[Collection type] = "Amicable"
   )
VAR BalancePart =
   CALCULATE(
       SUM ( 'Fact Ledger'[Ledger Balance] ),
       'd_balance_mappings'[Balance Type] IN { "Balance", "Costs" },
      TREATAS ( SelectedCollectionIDs, 'Fact Ledger'[Collection_ID] )
   )
VAR PaymentPart =
   CALCULATE(
       SUM ( 'Fact Ledger'[Ledger Balance] ),
       'd_balance_mappings'[Balance Type] = "Payments",
       TREATAS ( SelectedCollectionIDs, 'Fact Ledger'[Collection_ID] )
   )

RETURN
   BalancePart - PaymentPart