Forum Discussion
Dual Filter on Poorly Structure Data in a Matrix Report
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.
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
3 Replies
- Nathaniel_C
Community Champion
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.
NathanielThanks 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.
- Ashish_Mathur
Super User
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.
