topic Re: Measure with Calculate/filter over 2 different tables in DAX Commands and Tips

Measure with Calculate/filter over 2 different tables

Anonymous — Wed, 17 Nov 2021 14:08:41 GMT

Dear co-users,

I am using PowerBI for several months now, had a course and learned a lot from this forum. But I'm still a beginner and this issue I cannot solve, nor find a solution on the internet:

In my model, there are 2 measures that are calculated over different tables. These measures are plotted in a line graph. Seperately they work fine, but when added it gives strange results. As you can see the **bleep**. booked part is not taken righly in the new measure:

Measure 1 :

Booked = Calculate( sum('GLS actual'[Booked]),

Filter(allselected('GLS Actual'),

'GLS actual'[Posting Date]<=max('GLS actual'[Posting Date])))

Measure 2:

Committed = Calculate( sum(Committment[Commitment]),

Filter(ALLSELECTED(Committment),

'Committment'[Debit date]<=max(Committment[Debit date])))

To add them, (measure 3) I just add the measures itself, or copy the formulas:

assigned =

Calculate( sum('GLS actual'[Booked]),

Filter(allselected('GLS Actual'), 'GLS actual'[Posting Date]<=max('GLS actual'[Posting Date])))

+Calculate(

sum(Committment[Commitment]),

Filter(ALLSELECTED(Committment),

'Committment'[Debit date]<=max(Committment[Debit date])))

How can I add measures 1 and 2 properly?

Re: Measure with Calculate/filter over 2 different tables

Anonymous — Fri, 12 Nov 2021 09:51:45 GMT

Hello Anonymous

You can try the below code.
Assigned = Booked + Committed

Re: Measure with Calculate/filter over 2 different tables

Anonymous — Fri, 12 Nov 2021 10:19:47 GMT

Hi Tarun, I tried that, but gives the same result

Re: Measure with Calculate/filter over 2 different tables

Anonymous — Fri, 12 Nov 2021 11:12:01 GMT

Hello Anonymous

Please check the relationship.
If it doesn't work for you please share the model after removing the sensitive data.

Thank You

Re: Measure with Calculate/filter over 2 different tables

Anonymous — Tue, 16 Nov 2021 05:53:49 GMT

Hi Anonymous,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Re: Measure with Calculate/filter over 2 different tables

Anonymous — Wed, 17 Nov 2021 14:10:19 GMT

The information is not to be public, so I cannot share. Meanwhile I found a solution from a colleague:

**bleep**.assigned = CALCULATE([**bleep**. Booked], filter(allselected('Date'),

'Date'[Date]<=max('Date'[Date]))

)+ CALCULATE([**bleep**. Committed], filter(allselected('Date'),

'Date'[Date]<=max('Date'[Date])))

Re: Measure with Calculate/filter over 2 different tables

Anonymous — Thu, 18 Nov 2021 06:06:31 GMT

Hi Anonymous,

Ok, thanks for sharing the solution. I think it will be helpful if others faced a similar scenario.

Regards,

Xiaoxin Sheng