Forum Discussion

Veronika3's avatar
Veronika3
Regular Visitor
8 months ago
Solved

Duplicate total despite the filter used

Hello,
I need help with this measure. 

I have a measure that works correctly only if I do not filter by country. This is how it looks when it’s correct:
Meta test = IF (
    SELECTEDVALUE ( filterHide[Text] ) = "Hide",
    BLANK (),
    CALCULATE (
        SUM ( factMetaCampaign[SpendEur] ),
        TREATAS ( VALUES ( relCampaignSUParticipation[generatedID] ),       factMetaCampaign[GeneratedID_PPID] ),
        TREATAS ( VALUES ( factMetaCampaign[dimSalesUnitBk] ),              factMetaCampaign[dimSalesUnitBk] ),
        TREATAS ( VALUES ( relCampaignSUParticipation[Partner] ),           factMetaCampaign[PartnerSUParticipation] )
    ))
in visual:

But when I want to select only factMetaCampaign[dimSalesUnitBk] - the country, for example, the Czech Republic, the metric sums all SpendEur regardless of relCampaignSUParticipation[Partner]. So when I select TON GROUP and filter by country CZ, it should display the number 1,920; instead, it sums all countries for the partner TON GROUP. Can you please help me?
The MAX condition cannot be used to select only the first row either, because I need to sum the partners.

Thank you so much. 


 



  • tamerj1's avatar
    tamerj1
    8 months ago

    Hi Veronika3 

    Please try

    Meta test =
IF (
    SELECTEDVALUE ( filterHide[Text] ) <> "Hide",
    CALCULATE (
        SUM ( factMetaCampaign[SpendEur] ),
        KEEPFILTERS (
            TREATAS (
                VALUES ( relCampaignSUParticipation[generatedID] ),
                factMetaCampaign[GeneratedID_PPID]
            )
        ),
        KEEPFILTERS (
            TREATAS (
                VALUES ( relCampaignSUParticipation[Partner] ),
                factMetaCampaign[PartnerSUParticipation]
            )
        )
    )
)

8 Replies

  • Murtaza_Ghafoor's avatar
    Murtaza_Ghafoor
    Icon for Super User rankSuper User
    8 months ago

    The issue happens because the measure uses TREATAS on a country column that already exists in the same table (factMetaCampaign[dimSalesUnitBk]).
    When this is done, DAX removes the existing country filter and reapplies it in a way that breaks the normal filtering behavior.

    As a result, when you filter by a country (for example, Czech Republic), the measure still adds up spend from all countries for the selected partner instead of only the selected one.

    TREATAS should only be used to apply filters from one table to another, not on columns that already belong to the table being summed.

    Once the self-referencing TREATAS is removed, the country filter works correctly and the total for each partner is calculated as expected (for example, TON GROUP shows 1,920 for CZ).

    To sum up all,avoid using TREATAS on columns from the same fact table—let Power BI’s natural filter context handle them.

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      8 months ago

      Murtaza_Ghafoor 

      You are absolutely right. In such scenarios I would prefer to use a no CALCULATE approach. In fact a no CALCULATE approach is always my first choice. 
      The issue can be simply resolved using 

      Meta test =
IF (
    SELECTEDVALUE ( filterHide[Text] ) <> "Hide",
    SUMX (
        FILTER (
            factMetaCampaign,
            factMetaCampaign[GeneratedID_PPID]
                IN VALUES ( relCampaignSUParticipation[generatedID] )
                    && factMetaCampaign[PartnerSUParticipation]
                        IN VALUES ( relCampaignSUParticipation[Partner] )
        ),
        factMetaCampaign[SpendEur]
    )
)
  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    8 months ago

    Hi Veronika3 

    Please try

    Meta test =
IF (
    SELECTEDVALUE ( filterHide[Text] ) <> "Hide",
    CALCULATE (
        SUM ( factMetaCampaign[SpendEur] ),
        KEEPFILTERS (
            TREATAS (
                VALUES ( relCampaignSUParticipation[generatedID] ),
                factMetaCampaign[GeneratedID_PPID]
            )
        ),
        KEEPFILTERS (
            TREATAS (
                VALUES ( relCampaignSUParticipation[Partner] ),
                factMetaCampaign[PartnerSUParticipation]
            )
        )
    )
)
  • anilelmastasi's avatar
    anilelmastasi
    Icon for Super User rankSuper User
    8 months ago

    Hello Veronika3 ,

     

    You should never use tretas to map a column to itself. It removes filter context. You should remove  

    TREATAS ( VALUES ( factMetaCampaign[dimSalesUnitBk] ),              factMetaCampaign[dimSalesUnitBk] ),

    part.

     

    If this solved your issue, please mark it as the accepted solution. ✅

  • Veronika3's avatar
    Veronika3
    Regular Visitor
    8 months ago
    Unfortunately, even after removing the relevant syntax, the correct value still did not appear when filtering by country.
    Meta test= IF (
        SELECTEDVALUE ( filterHide[Text] ) = "Hide",
        BLANK (),
        CALCULATE (
            SUM ( factMetaCampaign[SpendEur] ),
            TREATAS ( VALUES ( relCampaignSUParticipation[generatedID] ),       factMetaCampaign[GeneratedID_PPID] ),
            TREATAS ( VALUES ( relCampaignSUParticipation[Partner] ),           factMetaCampaign[PartnerSUParticipation] )
        ))
  • Veronika3's avatar
    Veronika3
    Regular Visitor
    8 months ago



    I was closest to the correct calculation here. However, it still did not include the country, so it was incorrectly summed.


    IF (
        SELECTEDVALUE ( filterHide[Text] ) = "Hide",
        BLANK (),
        SUMX (
            VALUES ( relCampaignSUParticipation[Partner] ),
            CALCULATE (
                SUM ( factMetaCampaign[SpendEur] ),
                KEEPFILTERS ( VALUES ( factMetaCampaign[dimSalesUnitBk] ) )
                   )
        ))

     

     

    • Praful_Potphode's avatar
      Praful_Potphode
      Icon for Super User rankSuper User
      8 months ago

      Hi Veronika3 ,

      Please try below measure:

      Meta test =
IF (
    SELECTEDVALUE ( filterHide[Text] ) = "Hide",
    BLANK (),
    SUMX (
        SUMMARIZE (
            relCampaignSUParticipation,
            relCampaignSUParticipation[Partner],
            relCampaignSUParticipation[Country]
        ),
        CALCULATE (
            SUM ( factMetaCampaign[SpendEur] ),
            KEEPFILTERS ( VALUES ( factMetaCampaign[dimSalesUnitBk] ) )
        )
    )
)
      Meta test Treat as=
IF (
    SELECTEDVALUE ( filterHide[Text] ) = "Hide",
    BLANK (),
    CALCULATE (
        SUM ( factMetaCampaign[SpendEur] ),
        TREATAS ( VALUES ( relCampaignSUParticipation[Partner] ), factMetaCampaign[PartnerSUParticipation] ),
        TREATAS ( VALUES ( relCampaignSUParticipation[Country] ), factMetaCampaign[Country] ),
        TREATAS ( VALUES ( factMetaCampaign[dimSalesUnitBk] ), factMetaCampaign[dimSalesUnitBk] )
    )
)

      If it doesnt work,please provide a clear snapshot of input/output.

       

      Please give kudos or mark it as solution once confirmed.

       

      Thanks and Regards,

      Praful