Help needed - DAX measure subcategory

[Anonymous]

Hi All,

I recently came accross an issue in one of my critical projects that prevents me from successfully closing it - so after depleting all other options I decided to reach out for help. The problem has to be solved in a DAX measure, in which I am unfortunately not very experienced in.

I have one large table (feeding from Azure, updated daily) that contains different sales order lines. Each sales order line contains 1 material code - but 1 material code can of course be part of multiple sales orders. A sales order line can also of course pop up multiple times, but only once each day. The fact that its part of this dataset in a day tells that its a problematic order.

One of our most important ways to view this in our supply chain, is to look at material-level summary, to get an idea that how many time did material + warehouse combinations popped up in these orders. It looks like this: 

The user picks date(s) at the top, and everything works like a charm. We use the colored column for prioritization, which is a simple distinct count of sales doc codes. Each material and warehouse only pops up once in a line (otherwise the prioritization would completely lose point). The big problem, is that some fields, like Country, Customer, and such, are not unique to the material + warehouse, but can be different accross the sales orders. In the table, we need to show the one that is affected by the greatest number of order lines - for example, in the first row, PL10 should be shown, if PL10 has the biggest share of all company codes from the 38 orders (red column). Here we used FIRST so that at least it does not split up the lines - but in quite a few cases, of course the FIRST is not the biggest one. 

Hence I would need a measure, that I could create for each of these "line-splitting" fields, like the customer, so that it picks up the one that in the selected context (users filter a lot, to dates, to other things.. but we only have a single table, and everything else is straightforward) corresponds to the biggest number of order lines - so that we quickly see the top customer affected.

I made a few very far-fetched attempts to do this, all of them were failures, like this one, where the idea was that I create a virtual table that lists the customers related to the specific material + wh, and then I select the one that has the largest number - unfortunately here the value it returns is the same as the distinct order count (red one above) as the distinctcount within the measure doesnt count related to the customer.

Topcustomer= FIRSTNONBLANK( SELECTCOLUMNS(SELECTCOLUMNS(SELECTCOLUMNS('Data all',"Customer",'Data all'[Customer], "Key", DISTINCTCOUNT( 'Data all'[backorder_key])),"Customer2",[Customer],"Key2",MAX([Key])),"ultratest",[Key2]), 0)

Happy to answer any questions, would be grateful for any help to build this measure.. Unfortunately no workaround is possible, it has to be literally in the table as users need to export it quickly to Excel.

Thanks,

Andersen

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

Give this a try for me. 

Add a measure for the bo lines distinct count.

BO lines distinct count = DISTINCTCOUNT( 'Data all'[backorder_key] )

This measure should calculate the top Company Code for a line base on your [BO lines distinct count] measure.

Top Company Code =
CALCULATE (
    SELECTEDVALUE ( 'Data all'[Company Code] ),
    TOPN ( 1, ALL ( 'Data all'[Company Code] ), [BO lines distinct count] )
)

 Top customer would be like this.

Top Customer =
CALCULATE (
    SELECTEDVALUE ( 'Data all'[Customer] ),
    TOPN ( 1, ALL ( 'Data all'[Customer] ), [BO lines distinct count] )
)

[Anonymous]

Hi @jdbuchanan71 

thanks a lot for your help. It worked almost perfectly - only thing I needed to adjust, was to put FIRSTNONBLANK around your Top() part, because your formula gave no result when there were multiple fields with equal number of corresponding lines. 

Much appreciated,

Andersen

P.S.: could you recommend a good course on DAX where I could learn these things? I try to Google and read the documentation, but it is very difficult for me to understand - although I am confident in SQL and such.

[jdbuchanan71]

sqlbi.com has good courses on DAX and they do a lot of lectures that they post up as well.