topic Re: LastNonBlank value & target value on gauge in DAX Commands and Tips

LastNonBlank value & target value on gauge

Anonymous — Mon, 17 Feb 2020 09:09:27 GMT

Sorry for maybe such a basic problem of mine, I'm currently in the learning curve but can't figure out DAX measure so simply yet.

I'v got data sample as following:

Would like to vizualize two gauges, actual vs. target values:

- latest non blank 'BOMValue' vs. respective 'BOMTargetValue' (for 'Detector' device only)

- same for 'Bridge' device only

In my mind, it shoud be solved by DAX codes, right? Something like:

Latest Detector BOM = VAR CurrentBOMValue = LASTNONBLANK ('UnitTable'[BOMValue], 0)

RETURN CALCULATE (LASTNONBLANK(UnitTable[BOMValue], 0))

Thanks for a million

Re: LastNonBlank value & target value on gauge

sturlaws — Mon, 17 Feb 2020 22:00:13 GMT

Hi Anonymous

writing your measures like this is sufficient:

Last BOM Value = LASTNONBLANK('Table'[Bom value];0)

and then add the field Detector/Bridge to the visual filter pane of each gauge, and set it to filter to equal to detector and bridge, respectively

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

Re: LastNonBlank value & target value on gauge

Anonymous — Tue, 18 Feb 2020 10:32:35 GMT

sturlaws

thank you for help, now I understand the idea how to solve it, but this formula gives me errors and I don't know why:

The syntax for ';' is incorrect. (DAX(LASTNONBLANK(UnitTable[BOMValue];0)))

Re: LastNonBlank value & target value on gauge

Anonymous — Tue, 18 Feb 2020 12:13:15 GMT

sturlaws

Never mind, I figured out the formula problem, it should have "," instead of ";"

Now I see values on gauge but the next problem is that formula is not giving value of last non blank (last date) but maximum value from column instead

Re: LastNonBlank value & target value on gauge

sturlaws — Wed, 19 Feb 2020 17:49:27 GMT

sorry, tested on data that were to simplified, so it gave the correct answer for the wrong reason.

Can you try this code instead:

Last BOM Value = CALCULATE ( SUM ( Data[BOM Value] ), LASTNONBLANK ( 'Data'[Date], CALCULATE ( SUM ( Data[BOM Value] ) ) ) )

Idealy, 'Data'[Date] should reference a date/calendar table, so if you have one of those, replace this part of the code.

Re: LastNonBlank value & target value on gauge

Anonymous — Sat, 22 Feb 2020 20:57:56 GMT

sturlaws

Thank you so much, you helped me to understand the logic how I should think in PBI!