Lookupvalue in calculated column power query not working

[rlymer]

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:

It is all working and I am getting the expected results EXCEPT for Audits which are coming out as Standalone.

Any ideas?

[v-yanjiang-msft]

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

[rlymer · 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.

[rlymer]

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

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.

[v-yanjiang-msft]

Hi @rlymer ,

Please try to change the cross filter direction to Both.

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]

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.