List of un-matched values pivoting by Dimension

[ShaeParticipant Started: 1Replies: 1Total: 2]

I am relatively new to data modeling with Dax and PowerPivot. I have been working my way through books and forums as I get a handle on moving from the old excel way to the new. I am working on a problem that i havent yet figured out how to crack.

I have two main fact tables Sales Invoices & Purchase Invoices. Both tables have Ledger Entry columns that act as squencers. Both have Invoice No. that are their primary keys. The only field they have in common is an original Sales Order No.

The problem I have is that these are both “Many”. A Sales Order at time of invoicing may end up with more than one single invoice. Similarly when Freight Purchase Invoices are applied a second invoice may be tied back to the Sales order at a later date.

I need to answer the questions, “Which invoiced Sales Orders do not yet have Freight Invoices tied to them?” & “Which Freight Carrier should I be expecting a bill from?”

 

I have built a Bridge table creating a unique list of Sales Orders which is related to both the Sales Invoice & Purchase Invoice Tables. I have been pouring through forums here and on SQLBI and I have found inferences of pulling a list like this out being easy but not a good explanation of how to go about it.

 

I have tried creating a Calculated Column in the Sales Invoice Table so that I can sort the list by a boolean Invoiced yes/no. With riffs on CALCULATE, LOOKUPVALUE, TOPN.

 

I have tried associating the first Purchase Invoice with the Unique Sales Order in the bridge in order to sort and count the blanks.

 

I am hoping someone here can tell me if i am thinking in the right direction Or redirect

I have attached a paired down version of my data model with a tab of the results I would expect to get highlighted.

I am running Office Professional 2016.

 

Any and all help is highly appreciated.

 

Thank You,

Shae

 

 

 

 

FYI: The books i am referencing daily are:

Power Pivot & Power BI by  Rob Collie & Avichal Singh

The Definitive Guide to Dax by Marco Russo & Alberto Ferrari

M – Is for Data Monkey by Ken Puls & Miguel Escobar

 

Attachments:

You must be logged in to view attached files.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Shae,

First, all three of the books you have listed are definitely some of the best available. If you use Excel 2016 or Power BI, some of “M is for Data Monkey” is obsolete, namely the feature for importing multiple files from a single folder. However, Puls and Escobar are scheduled to release the next edition of the book with the title “Master Your Data with Excel and Power BI: Leveraging Power Query to Get & Transform Your Task Flow” in November of this year (2018).

Looking at your data model, there is an issue with your bridge table because it uses a whole number data type for the sales order number, while the corresponding values in sales and purchase invoice tables are text. This will definitely affect your results when using the bridge table.

Since Power Query uses a source for the bridge table that I cannot access, I will create a modified solution using only the fact tables.

Initially, a calculated column (Pending Freight Order) will be added to the sales invoices table with the following formula:

=
IF (
    COUNTROWS (
        FILTER (
            'Purchase_Invoices',
            'Purchase_Invoices'[Freight Sales Order] = 'Sales_Invoices'[Sales Order No_]
        )
    )
        = 0,
    TRUE (),
    FALSE ()
)

Then a measure can be created:

Estimated Freight For Pending Freight Orders :=
SUMX (
    FILTER ( Sales_Invoices, Sales_Invoices[Pending Freight Order] ),
    Sales_Invoices[Estimated Freight]
)

Then a pivot can be created using just the new measure and columns from the sales invoice table (Carrier Code, Sales Invoice No_, Ship-to Country).

However, in the attached workbook, my pivot shows also shows a sales invoice number (sales order number) that was not found in the bridge table nor the purchase order table. I took this matter to be an issue with the bridge table (that it should have had the missing sales order number) instead of an issue with the measure. If you truly only want to use sales invoice records that have a matching record in the bridge table, let me know and I will make the corrections.

Tom

Attachments:

You must be logged in to view attached files.

[ShaeParticipant Started: 1Replies: 1Total: 2]

Tom,

Thank you for the response! I have looked over your revised file and think i understand better now how you went about this. Especially after re-reading Chapter 13 of ““Power Pivot and Power BI” on FILTER().  This really is an elegant solution. I can see how that IF( COUNTROWS ( FILTER))) expression pattern for boolean creation is really useful and simple. The added value of then being able to use that calculated column as a parameter within a future Measure already has my mind dancing with the possibilities.

 

RE: Differences in Expected Results – I noticed that in your Pivot you had pulled in “Sales Invoice No.” instead of “Sales Order No.”. I had the latter originally hidden from client tools. Once un-hid the column and flipped those around in the pivot I can see not only that your solution is showing my expected results, but also the expected result I missed myself.

 

Re: Books – Thank for the heads up on some of the info in “M is for Data Monkey” being obsolete. One of my next project builds is based around the data source tables being several iterations of “multiple files from a single folder”. Knowing that has been changed in 2016 will keep me from going down useless paths of research.

 

Re: Bridge Table – My original source does have the Sales order column set as Text for both fact and bridge tables. I thought I had scrubbed all the data types to be uniform when I built up my example, but obviously missed that one.

 

Re: How Power Query references a data source – When I built my example file I attempted to place the source data within named tables the workbook and then only build off of connections to those tables. I see where I made my mistake by pointing to my local file system and not using a “From Table” connection.

 

I am going to play around with this some, but i can already see that it gets me past my current hurdle in this analysis. Thanks again!

 

Shae

[Author]

Posts