Dual Filter on Poorly Structure Data in a Matrix Report

[tsg]

I am attempting to create some count metrics in a matrix report.  Unfortunately, the data is not well structured for the task and I’m running into some problems.

Data Structure in the table is sitting as follows:

Table ‘Order’

Order	Field_Code	Field_Data	Receive Week
1234	Division	SP
1234	Receive Date	43748	34
1234	Promise Date	43753
1235	Division	CH
1235	Receive Date	43749	35
1235	Promise Date	43755

Currently I’m utilizing the following formula to calculate the metric for orders received during a given week:

Received (CH) = VAR ReceiveCount =  Calculate(DISTINCTCOUNTNOBLANK('OE-ORDH'[Order Number]), Filter('OE-ORDH', 'OE-ORDH'[Received Week] = Max('Date'[WeekNumberYear]))) return ReceiveCount

The date table is a standard date table for the purpose of providing a weekly breakdown on the matrix.  This has gone through several iterations, the problem is when I attempt to add a secondary filter to filter by Division as well.  The filter to the receive week causes me to lose visibility to the division field code and corresponding field data no matter what I try.  I expect one measure per division to be necessary.

What I'm looking for is something along the lines of:

Received Count
	Week 1	Week 2	Week 3
Division SP	5	7	5
Division CH	6	8	6

Any help with this would be greatly appreciated.

[Ashish_Mathur]

Hi,

I assume 43748 is Oct 10, 2019.  Is that how your source data is i.e. instead of a Date there is a number?  Can that be changed to a Date in the source itself?  Also, From the Receive Date of 43748, how did you get week 34?  The Weeknum function returns 41.

[Nathaniel_C · Accepted solution]

Hi @tsg ,

Your data needs to be rearranged. You are really hurting yourself to try and keep it as is.  Once it is changed, your life will be so much simpler! Now I had an issue with pasting what you had given us, so I just changed it around in Excel.  You would be good to do this in Power Query.  I am not sure how much work that will be, but once it is done, you can refresh it when you get new data. Each column should only have one type of data in it. So for example you have Divisions and also the headers for other columns in the one column. (Since I did this by hand in Excel, some of the numbers may be in the wrong spot, but you get the idea.) 

If you think this is a good idea, I would repost your question in Power Query, and show your table and the new table, as before and after.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[tsg]

Thanks for the response.  The push to take care of the data at the query stage was exactly what I should have done from the start.