cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tsg Frequent Visitor
Frequent Visitor

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 1Week 2Week 3
Division SP575
Division CH686

 

Any help with this would be greatly appreciated.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Nathaniel_C Super Contributor
Super Contributor

Re: Dual Filter on Poorly Structure Data in a Matrix Report

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

Order.PNG

View solution in original post

3 REPLIES 3
Nathaniel_C Super Contributor
Super Contributor

Re: Dual Filter on Poorly Structure Data in a Matrix Report

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

Order.PNG

View solution in original post

Super User
Super User

Re: Dual Filter on Poorly Structure Data in a Matrix Report

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
tsg Frequent Visitor
Frequent Visitor

Re: Dual Filter on Poorly Structure Data in a Matrix Report

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.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)