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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Myshydde
Frequent Visitor

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 called “Category”, in which a text is added depending on conditions in the form of a “Code”. If no condition is found in my rules table, fetch the “DefaultCategory” from my suppliers table.


I have my Datatable as example below:

SupplierID

SupplierName

Amount

Code

Category

123

Name 1

1000

55

 

123

Name 1

500

33

 

555

Name 2

750

55

 

555

Name 2

750

44

 

 

I also have a table for my suppliers:

SupplierID

SupplierName

DefaultCategory

123

Name 1

Categoryname 1

555

Name 2

Categoryname 1

 

I also have a table for rules to override the DefaultCategory:

SupplierID

Code

NewCategory

123

33

Categoryname 2

555

44

Categoryname 3

 

I would like the result to return like this:

ID

SupplierName

Amount

Code

Category

123

Name 1

1000

55

Categoryname 1

123

Name 1

500

33

Categoryname 2

555

Name 2

750

55

Categoryname 1

555

Name 2

750

44

Categoryname 3

 

If anyone could help me I would really appreciate it.

2 ACCEPTED SOLUTIONS
SomeDataDude
Advocate I
Advocate I

Hi @Myshydde ,

 

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

 

SomeDataDude_0-1671118057437.png

 

View solution in original post

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])
    )



View solution in original post

10 REPLIES 10
SomeDataDude
Advocate I
Advocate I

Hi @Myshydde ,

 

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

 

SomeDataDude_0-1671118057437.png

 

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?

Hi @Myshydde ,

 

Could you provide a sample of your data and problem?

Hi,

Im embedding pictures of the different tables down below.

Data table:

Myshydde_0-1671440289590.png

 

Supplier table:

Myshydde_1-1671440367063.png

Rules table:

Myshydde_2-1671440425365.png

 

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:

Myshydde_3-1671440686556.png

 

 

 

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?

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

Myshydde_1-1671451101026.png

 


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. 

Myshydde_0-1671451071198.png

 

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

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.

I'm not allowed to upload a file here so try if you get access with this link: https://www.dropbox.com/sh/3lcca8y78bjtzet/AACqz8wG0nGPXXcSrGbu-F9Ma?dl=0  
I've added some code to my example, but I worry that all the LOOKUPVALUE() will be very taxing on the system once the real data with maybe 200-300 000 rows are calculated.

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])
    )



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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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