Forum Discussion

rajanimaddala's avatar
rajanimaddala
Helper II
5 years ago

DAX Measure issue

Hi

I have 2 tables in power bi desktop model.

 

Payments(PayDate, CostCode, Amount)

CostCodeMapping(CostCode, Region, Directorate)

I have 1 to many relationship between CCMapping and Payments

 

I have measure to calculate TotalAmount till the selected month(slicer)

 

Total Amount = CALCULATE(SUM(Payments[Amount]), FILTER(ALL(Payments), PayDate <= today() && Payments[CostCode] IN VALUES(CCMapping[CostCode])))

 

It is working fine when displayed in table with PayMonth, TotalAmount

 

Now, I have pulled Region in the table visual. Issue here...

TotalAmount for region is showing entire CostCode total not by Region

 

I thought I need to add one more clause in the DAX as Region in values(CCMapping[Region] but Payments doesn't have Region field

 

How can I fix the issue?

 

Thanks in advance

 

Need Help
Tips and Tricks

6 Replies

  • aj1973's avatar
    aj1973
    Community Champion
    5 years ago

    Hi,

    In your Model change the relationship from single to Both.

     

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

    rajanimaddala 

    Can you post an image of the final visual?

    I have a feeling you don't need the filter expression "&& Payments[CostCode] IN VALUES(CCMapping[CostCode])", given you have aone-to-many relationship bewteen your tables.

     

     

  • aj1973's avatar
    aj1973
    Community Champion
    5 years ago

    Can you share a dummy of your PBIX please?

    • aj1973's avatar
      aj1973
      Community Champion
      5 years ago

      Great,

      What was the issue?

      Please mark the post as Solved for the rest of the community