Measure causing sum not to work.

[larryc]

Hello,

Here is the report I am working on: DOWNLOAD

Problem:  When the report date changes, I must be able to supply the correct exchange rate as of that selected report date. This forces me to use a measure as calculated columns can not contain dynamic user selected data as they are loaded at runtime.

So if you look in the report XRATE supplies the correct exchange rate as of the report date selected by the end user. The problem is that any other measure that calculates based on this XRATE measure will NOT sum.

Can anyone figure out what is happening or is the DAX engine simply broken at this time?

Thanks

[Vvelarde]

@larryc

Hi, The issue is the IF(HASONEVALUE....) in your measure.

So when the total is evalualted the HASONEVALUE is FALSE the measure dont apply your calc.

I'm going out to the office but try to modify or reevaluate this and will work.

Regards

Victor

Lima - Peru

[lcasey]

Vvelarde,

In all my reading, you MUST use HasOneValue . There is simply no other option and there are no other calculations that can lookup a value in another table based on the report date without using HasoneValue.

Every single instance I have reviewed on the internet requires HASONEVALUE

I beleive I have just stumped the entire DAX community!   This is certainly a BUG!!!!!

[lcasey]

If I use this formula: 

Xrate = IF(MAX('00-Bad_ Debt_Invoices'[CURNCYID]) = "USD",1, LOOKUPVALUE('00-Bad_Debt_MC'[XCHGRATE],'00-Bad_Debt_MC'[CURNCYID],Max('00-Bad_ Debt_Invoices'[CURNCYID]),'00-Bad_Debt_MC'[EXCHDATE],MAX('Calendar'[Date])))

then the totals are completely wrong.

I HATE DAX!!!!!

[larryc]

Hello Vvelarde,

Thanks, but everything just turns to 0 if I add a number, or if I use a 1 the entire sum balance is incorrect.