topic Re: compare table values with a measure does not work in DAX Commands and Tips

compare table values with a measure does not work

Anonymous — Mon, 09 Sep 2019 09:20:46 GMT

Hello all,

I have observed a behaviour in DAX that I can't understand.

I want to compare the "Leadtime" column in a table to determine the elements with "Leadtime" greater than a threshold:

00_IsLTmorethanT2 = ('AO Input'[Leadtime]> 'AO Input'[10_minLT]) #this does not work

'AO Input'[Leadtime] is a column

'AO Input'[10_minLT] is a measure (copied at the end of this message)

The expression written this way does not work.

It works if instead of an expression I compare against a constant value:

00_IsLTmorethanT2 = 'AO Input'[Leadtime]> 360 #this works

How can I fix this? Thanks for your help.

The expression calculating 10_minLT is this (it is a workaround to let the user input a parameter using a filter)

10_minLT =

IF (

HASONEVALUE (T2[Material Lead Time]);

VALUES (T2[Material Lead Time]);

180)

Re: compare table values with a measure does not work

AlB — Mon, 09 Sep 2019 10:04:32 GMT

Hi Anonymous

Can you share the pbix, or a simplified version thereof that reproduces the issue?

Re: compare table values with a measure does not work

Anonymous — Mon, 09 Sep 2019 11:29:40 GMT

AlB Thanks for replying.

yes, sure I can share a reduced version.

What is the recommended way of sharing pbix? I checked out the "using the community" forum but did not find instructions there.

(maybe a hint for Anonymous to add a "how to share a pbix with the community" there).

Many thanks and regards.

Re: compare table values with a measure does not work

AlB — Mon, 09 Sep 2019 11:39:49 GMT

Anonymous

Sounds like a good idea. Many people actually ask that.

You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).

Re: compare table values with a measure does not work

Anonymous — Mon, 09 Sep 2019 13:07:32 GMT

Hello AlB ,

I'll do with onedrive:

link to folder

In the folder I added the pbix and the data source (an excel sheet).

Thanks again,

Re: compare table values with a measure does not work

AlB — Mon, 09 Sep 2019 14:11:39 GMT

Hi Anonymous

You have a calculated column in your table . That is static and there is no filter context; that's why your measure will always yield 180 HASONEVALUE is always FALSE. You need to create a measure and use it in the table visual:

00_IsLTmorethanT2_Measure = (SELECTEDVALUE('AO Input'[Ges. Beschaffungszeit]) > 'AO Input'[10_minLT2])

and get rid of the 00_IsLTmorethanT2 column. See attached file

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers

Re: compare table values with a measure does not work

Anonymous — Mon, 09 Sep 2019 15:00:38 GMT

Great thanks AlB , it works great and I accepted your reply as solution.

Actually, in my full report I still have the issue that the comparison result is not updated. That report is much more complicated, with different tables & relations among them.

I'll try sorting it out myself, keeping in mind that calculated columns are static.

Thanks again and have a nice day!