cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
domenicomu Frequent Visitor
Frequent Visitor

compare table values with a measure does not work

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)
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: compare table values with a measure does not work

Hi @domenicomu 

 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  Datanaut

View solution in original post

6 REPLIES 6
Super User
Super User

Re: compare table values with a measure does not work

Hi @domenicomu 

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

domenicomu Frequent Visitor
Frequent Visitor

Re: compare table values with a measure does not work

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

Super User
Super User

Re: compare table values with a measure does not work

@domenicomu 

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

domenicomu Frequent Visitor
Frequent Visitor

Re: compare table values with a measure does not work

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,

Highlighted
Super User
Super User

Re: compare table values with a measure does not work

Hi @domenicomu 

 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  Datanaut

View solution in original post

domenicomu Frequent Visitor
Frequent Visitor

Re: compare table values with a measure does not work

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!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 276 members 2,764 guests
Please welcome our newest community members: