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
PBCIT
Frequent Visitor

Getting Correct Item Number

Ok so I have a problem I've been trying to solve for a while now. I have a few tables that I am using. The first is from a database that a third party created and it is what I use to get Invoice data from. In our ERP system, each item is given an item number for identification, but item numbers can be reused. In the database I use, this is accounted for by giving each item a separate product id to account for different versions of the item. I then have reports that record the item number, but since there is no product id in those reports, I'm having a hard time pairing the data and getting the item that was used at the time of purchase. Here is a sample of the data and what I have currently done (Data is fake):

From Database:

Product IDItem NumberDescriptionLastDateInvoiced
21567800001Hot Dog8/1/2020
21567900002Buns8/1/2020
21568000001Hot Dog Jumbo9/18/2021
21568100002Buns 8 Pack

9/19/2021

 

From Report:

Date Returned:Item Number
6/7/202000001
7/31/202000002
2/1/202100001

 

How can I get it that the right description will fill in based on the Item Number and the date returned? Anyone have any thoughts?

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

Hey @PBCIT ,

 

I use this DAX statement to create a calculated column inside the report table:

Column = 
var ItemNumber = 'report'[Item Number]
var DateReturned = 'report'[Date Returned]
var previousDate = MAXX(FILTER( SUMMARIZE( 'database' , database[Item Number] , database[LastDateInvoiced] ) , 'database'[Item Number] = 'database'[Item Number] && 'database'[LastDateInvoiced] < DateReturned ) , database[LastDateInvoiced] )
var checkPreVDate = IF( ISBLANK( previousDate ) , DateReturned , previousDate + 1)
var nextDate = MINX(FILTER( SUMMARIZE( 'database' , database[Item Number] , database[LastDateInvoiced] ) , 'database'[Item Number] = 'database'[Item Number] && 'database'[LastDateInvoiced] >= DateReturned ) , database[LastDateInvoiced] )
var _item = MAXX( FILTER( 'database' , 'database'[Item Number] = ItemNumber && ( 'database'[LastDateInvoiced] >= checkPreVDate && database[LastDateInvoiced] <= nextDate ) ) ,[Description] ) 
return
_item

What this statement does (or at least should do) is the following: determine the timeframe when an item was used based on the date returned column in the report table. These dates are stored in the variables checkPreviousDate and nextDate. Then these dates are used to filter for the description.

Based on the sample data you provided the report will look like this:

image.png

Please be aware, that this kind of calculation can become slow, depending on the size of the database table and the number of versions, this is due to the fact that SSAS Tabular, the underlying database engine of the Power BI data model does not know the data type sequence.

You may have to consider creating the item/description columns outside of Power BI and use a relational database in between (some kind of data warehouse).

 

Hopefully, this provides what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

2 REPLIES 2
v-luwang-msft
Community Support
Community Support

Hi @PBCIT ,

Use the following dax to create a new column:

describution = 
VAR maxdate =
    CALCULATE (
        MAX ( 'Table'[LastDateInvoiced] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[LastDateInvoiced] > 'Table (2)'[Date Returned:]
                && 'Table'[Item Number] = 'Table (2)'[Item Number]
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Description] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Item Number] = 'Table (2)'[Item Number]
                && 'Table'[LastDateInvoiced] = maxdate
        )
    )

Output result:

vluwangmsft_0-1632796442473.png

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

TomMartens
Super User
Super User

Hey @PBCIT ,

 

I use this DAX statement to create a calculated column inside the report table:

Column = 
var ItemNumber = 'report'[Item Number]
var DateReturned = 'report'[Date Returned]
var previousDate = MAXX(FILTER( SUMMARIZE( 'database' , database[Item Number] , database[LastDateInvoiced] ) , 'database'[Item Number] = 'database'[Item Number] && 'database'[LastDateInvoiced] < DateReturned ) , database[LastDateInvoiced] )
var checkPreVDate = IF( ISBLANK( previousDate ) , DateReturned , previousDate + 1)
var nextDate = MINX(FILTER( SUMMARIZE( 'database' , database[Item Number] , database[LastDateInvoiced] ) , 'database'[Item Number] = 'database'[Item Number] && 'database'[LastDateInvoiced] >= DateReturned ) , database[LastDateInvoiced] )
var _item = MAXX( FILTER( 'database' , 'database'[Item Number] = ItemNumber && ( 'database'[LastDateInvoiced] >= checkPreVDate && database[LastDateInvoiced] <= nextDate ) ) ,[Description] ) 
return
_item

What this statement does (or at least should do) is the following: determine the timeframe when an item was used based on the date returned column in the report table. These dates are stored in the variables checkPreviousDate and nextDate. Then these dates are used to filter for the description.

Based on the sample data you provided the report will look like this:

image.png

Please be aware, that this kind of calculation can become slow, depending on the size of the database table and the number of versions, this is due to the fact that SSAS Tabular, the underlying database engine of the Power BI data model does not know the data type sequence.

You may have to consider creating the item/description columns outside of Power BI and use a relational database in between (some kind of data warehouse).

 

Hopefully, this provides what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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.