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,

 

Okay, could you change the first line with LOOKUPVALUE() to:

 LOOKUPVALUE(Rules[Code], Rules[Code], 'Datatable'[Code]) = 'Datatable'[Code]

This is because your relationship is matching on more suppliers. But there can be only one matching value. In your rules table there are more matching values now. The code above is looking for one matching value in the code column. That is almost the same way you created with the CONCATENATE().

Did you made your relationship between the two tables on the suppliers column or on the code column?

Will you let me know if this works?

Myshydde's avatar
Myshydde
Frequent Visitor
3 years ago

The relationship of the tables are setup on SupplierID - with one to many from the supplier table to both other tables:

 


If I change the code as you mention, it now returns a match even if there is none. (Added a new column for testing "Category2"). There is no rule for "Supplier 3" in the rules table. 

 

I really do appreciate you taking your valuable time to help me!

  • SomeDataDude's avatar
    SomeDataDude
    Advocate I
    3 years ago

    Could you provide a sample .pbix file? Then I could test some things. Because I thought you connected Rules to Data and Suppliers to Data, but I was wrong.

      • SomeDataDude's avatar
        SomeDataDude
        Advocate I
        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])
    )