DAX Measure issue

[rajanimaddala]

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

[rajanimaddala]

Thank you all. I have fixed the issue

[aj1973]

Great,

What was the issue?

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

[PaulDBrown]

@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]

Can you share a dummy of your PBIX please?

[aj1973]

Hi,

In your Model change the relationship from single to Both.

[rajanimaddala]

Hi,

I have changed the relationship to Both but still no luck