compare table values with a measure does not work

[Anonymous]

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)

[AlB · Accepted solution]

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  

[Anonymous]

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!

[AlB]

Hi @Anonymous 

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

[Anonymous]

@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 @BrandyG_iTalent to add a "how to share a pbix with the community" there).

Many thanks and regards.

[AlB]

@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).

[Anonymous]

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,