topic Re: Calculated column rows do not return values for all rows in DAX Commands and Tips

Calculated column rows do not return values for all rows

Sai_Alkesh — Tue, 22 Mar 2022 05:38:20 GMT

Hi,

I have 2 tables - Currency & Sales and they are connected by many to many relationship.

I have created a calculated column in Sales table, and it returns value for only one condition, where the period is equal to '20214'. For other rows, where the period is <> 20214, the values are blank. Below is my table structure & the DAX

Table Currency -

Field LCurrency FCurrency FXRate Type Period

yyyym EUr GBp 0.8545 AVRS 20214

USd GBp 1.1684 AVRS 20214

JPy Eur 0.8808 RNMJ 20214

Table Sales

Field Item Currency Sales Period Calculated Column Value

yyyym 0123 USd 10000 20214 8558.712

4567 USd 8000 20213 BLANK, value should be 6846.97

8787 EUr 2000 20216 BLANK, value should be 2340.55

DAX is:

'SALES'[Sales] *

DIVIDE(1,

CALCULATE(

FIRSTNONBLANK('Currency'[FXRate], TRUE() ),

FILTER(

'Currency'

'Currency'[LCurrency] = 'Sales'[Currency] &&

'Currency'[FCurrency] = "GBP" &&

'Currency'[Type] = "AVRS" &&

'Currency'[Period] = "20214")

)

Appreciate your help on this please.

Thanks,

Re: Calculated column rows do not return values for all rows

Sai_Alkesh — Tue, 22 Mar 2022 07:05:51 GMT

amitchandak ; tamerj1 ; Jihwan_Kim

Appreciate your help

Re: Calculated column rows do not return values for all rows

amitchandak — Tue, 22 Mar 2022 07:10:36 GMT

Sai_Alkesh , If all rate rae there then you should compare period with period

'SALES'[Sales] *

DIVIDE(1,

CALCULATE(

FIRSTNONBLANK('Currency'[FXRate], TRUE() ),

FILTER(

'Currency'

'Currency'[LCurrency] = 'Sales'[Currency] &&

'Currency'[FCurrency] = "GBP" &&

'Currency'[Type] = 'sales'[Type] &&

'Currency'[Period] = sales[Period])

)

Re: Calculated column rows do not return values for all rows

Sai_Alkesh — Tue, 22 Mar 2022 07:40:57 GMT

amitchandak - it still gives the same result. The reason i have the period in the filter clause is, 'cos i want to get the inverse value of FX rate which is for that period. Once i get that rate, i want to multiply the sales from Sales table with this derived rate.

In the calculated column of Sales table, it shows me the result of the derived value, however it only shows where the period in sales table is '20214'

Re: Calculated column rows do not return values for all rows

amitchandak — Tue, 22 Mar 2022 08:11:20 GMT

Sai_Alkesh , do you have rates other than 20214.

Can share a little bigger sample to test

Re: Calculated column rows do not return values for all rows

Sai_Alkesh — Tue, 22 Mar 2022 08:50:06 GMT

amitchandak - yes, i do. The currency table has the budget rate which is set to period 20214 for various currencies, however they are valid for the full year.

In Sales tables, since we have sales by individual month, we want to multiply the monthly sales with the budget rate.

Re: Calculated column rows do not return values for all rows

amitchandak — Wed, 23 Mar 2022 05:30:02 GMT

Sai_Alkesh , if there valid for a full year.

I will add the year in both tables and join that instead of period

year = left([Period year],1)

Re: Calculated column rows do not return values for all rows

Sai_Alkesh — Wed, 23 Mar 2022 09:06:42 GMT

amitchandak , thanks for extending the support. I tried as suggested, however its the same output.

I even tried with lookupvalue, since Filter was iterative, hence leaving blanks for period other than 20214, however it doesnt work either.

Re: Calculated column rows do not return values for all rows

Anonymous — Fri, 25 Mar 2022 08:27:15 GMT

Hi Sai_Alkesh ，

Please remove any relationship between currency and sales table, and you can get the expected result...

Best Regards