Forum Discussion

Myshydde's avatar
Myshydde
Frequent Visitor
3 years ago
Solved

Help witch fetching values from another table depending on conditions

Hi I’m new do DAX and PowerBI and trying to get the hang of it, but I’m stuck. I will try to the best of my abilities describe what I need to do. I would like to create a new column in my datatable ...
Need Help
  • SomeDataDude's avatar
    SomeDataDude
    3 years ago

    Hi Myshydde ,

     

    If I understand you’re question. You’re looking for something like this?

     

     

  • SomeDataDude's avatar
    SomeDataDude
    3 years ago

    I add an extra search value in the LOOKUPVALE() to match the first criteria. In the first line were checking the rule code and supplier, if they match with Data[Code]. We return the NewCategory. Otherwise the DefaultCategory.

    Example:

        IF(
        LOOKUPVALUE(Rules[Code], Rules[SupplierIDCode], Data[SupplierIDCode], Rules[SupplierID], Data[SupplierID]) = Data[Code]
        ,
        LOOKUPVALUE(Rules[NewCategory],Rules[Code],Data[Code]) & "From Rules"
        ,
        LOOKUPVALUE(Suppliers[DefaultCategory],Suppliers[SupplierID],Data[SupplierID])
    )



SomeDataDude's avatar
SomeDataDude
Advocate I
3 years ago

Hi Myshydde ,

 

If I understand you’re question. You’re looking for something like this?

 

 

  • Myshydde's avatar
    Myshydde
    Frequent Visitor
    3 years ago

    Yes, this worked! Thank you for your swift answer!

  • Myshydde's avatar
    Myshydde
    Frequent Visitor
    3 years ago

    I've got myself in some trouble with this solution. While it works for single rows in the "Rules table", if there are multiple rules for the same supplier (other codes) then there is an error. I suppose I need another dimension of filters in the lookup?

    • SomeDataDude's avatar
      SomeDataDude
      Advocate I
      3 years ago

      Hi Myshydde ,

       

      Could you provide a sample of your data and problem?

      • Myshydde's avatar
        Myshydde
        Frequent Visitor
        3 years ago

        Hi,

        Im embedding pictures of the different tables down below.

        Data table:

         

        Supplier table:

        Rules table:

         

        What I've done to solve my problem is to create a new column, in both the Data and Rules table, with a CONCATENATE() of "SupplierID" and "Code", because it will always be a unique number in the Rules table. That way I could edit the LOOKUPVALUE() to match those values instead.

        Im sure there is a more elegant solution to this that dont add unecessary size to the model.
        See new example: