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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
rlymer
Frequent Visitor

Lookupvalue in calculated column power query not working

I am creating a calculated column using Lookupvalue to state what type action each row is. I am using the following in my power query:

 

rlymer_0-1665627689801.png

 

 
It is all working and I am getting the expected results EXCEPT for Audits which are coming out as Standalone.
 
Any ideas?
1 ACCEPTED SOLUTION

Hi All,

 

I found my problem.

 

A relationship between two unrelated tables had a filter going both ways rather than single. This was somehow filtering things incorrectly.

 

As soon as I changed it to single, the function started working.

View solution in original post

5 REPLIES 5
v-yanjiang-msft
Community Support
Community Support

Hi @rlymer ,

Is your problem solved? If so, would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirment will find the solution quickly and benefit here, thank you!

 

Best Regards,
Community Support Team _ kalyj

Hi All,

 

I found my problem.

 

A relationship between two unrelated tables had a filter going both ways rather than single. This was somehow filtering things incorrectly.

 

As soon as I changed it to single, the function started working.

rlymer
Frequent Visitor

@ImkeF That's what I thought. However when looking at tables of the two next to each other, there are definitely matching values e.g. 265954, 267758, 252026 etc.

rlymer_0-1665691053055.png

They have 2 columns have the same many to one relationship as all the other ID fields have to the ParentID so I can't see why only this one wouldn't be working.

 

rlymer_1-1665691410400.png

 

Hi @rlymer ,

Please try to change the cross filter direction to Both.

vkalyjmsft_0-1665739775089.png

Or modify the formula to:

var _isAudit=COUNTROWS(FILTER(Audits,Audits[$AuditsID]=Actions[$ParentID]))

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ImkeF
Community Champion
Community Champion

Hi @rlymer ,
your DAX code is looking syntactically correct.
So the reason why your are receiving the default-value must be that there is no match in your data with Audits.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.