Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
larryc
New Member

Measure causing sum not to work.

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

 

 

 

4 REPLIES 4
Vvelarde
Community Champion
Community Champion

@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




Lima - Peru

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!!!!!

 

 

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!!!!!

 

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.

 

01.png 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.