Forum Discussion

rlymer's avatar
rlymer
Frequent Visitor
3 years ago
Solved

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:

 

 

 
It is all working and I am getting the expected results EXCEPT for Audits which are coming out as Standalone.
 
Any ideas?
Need Help
  • rlymer's avatar
    rlymer
    3 years ago

    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.

5 Replies

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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.

  • rlymer's avatar
    rlymer
    Frequent Visitor
    3 years ago

    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's avatar
      v-yanjiang-msft
      Icon for Community Support rankCommunity Support
      3 years ago

      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.

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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's avatar
      rlymer
      Frequent Visitor
      3 years ago

      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.