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

Summarize across tables with 1 to many relationship in the wrong direction

I'm trying to create a table with SUMMARIZE, however it appears that my data don't have the correct relationships.  The tables are as follows (see below): with Import having a 1 to many relationship with Detail. Import is the data as recieved, with a start and end date. Because I'm trying to create a time series, I created the Detail table to give me the same value for each date between the start and end date. That's why the relationship goes from 1:M between Unique IDs in Import: Detail. However, when I try to summarize by date (I'm trying to figure out min over each calendar date, so min of jan 1, min of jan 2, etc), PowerBI doesn't let me do so because the relationship is incorrect.  I'm not sure how else I should have the relationship, because a 1:M relationship in the 'correct' direction doesn't make sense here? Or if there's a way around using the SUMMARIZE function to create something like SUMMARIZE(Import, Detail[Date], "Sum", SUM(Import[Value]))? Or, is there a better way to set up my data model in the first place?

 

If it helps, I'm using this to create a line chart, where each line is a  year and the x axis are each date in a calendar year. I'm planning on also plotting the min and max of each date from the overall dataset on this chart.

 

Import:

 

Unique IDValueStart DateEnd Date
34551/1/20146/1/2015
567751/1/20156/1/2016

 

Detail:

 

Unique IDDate
3451/1/2014
3451/2/2014
3451/3/2014
3451/4/2014
etcetc
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
Super User V

Re: Summarize across tables with 1 to many relationship in the wrong direction

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

View solution in original post

9 REPLIES 9
Highlighted
Community Support
Community Support

Re: Summarize across tables with 1 to many relationship in the wrong direction

Hi @ahavers ,

 

You can create a calendar table named DateKey , then create measure to get total value using DAX below. Finally, you can put the 'DateKey'[Date] onto Axis box, and put measure Total value onto Values box.

 

DateKey = CALENDARAUTO()
 
Total value = CALCULATE(SUM(Import[Value]),FILTER(ALLSELECTED(Import),Import[Unique ID]=MAX(Import[Unique ID])&&Import[End Date]<=MAX('DateKey'[Date])&&Import[Start Date]>=MIN('DateKey'[Date])))

If I misunderstood it, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Frequent Visitor

Re: Summarize across tables with 1 to many relationship in the wrong direction

hi @v-xicai 

 

Below is my sample pbix with the relationships as described. As you can see I'm trying to plot each year over each other on a plot of jan 1 - dec 31. I'm trying to figure out how to get the min/max of each day so I can plot those values as well. However it doesn't seem like I can summarize each date since the values and dates are in different tables (in file).

 

My expected output would be a line of min values and a line of max values that I can place on the main chart along with individual years. Thanks for any help.

 

https://1drv.ms/u/s!AuFhcdA3iSEvvWHfEXVhr-QfMDgC?e=sqx930

Highlighted
Super User V
Super User V

Re: Summarize across tables with 1 to many relationship in the wrong direction

Hi,

I am not clear about your question.  Show the result in a simple Table format.  Once that is finalised, any visual can be built.


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

Re: Summarize across tables with 1 to many relationship in the wrong direction

Hi @Ashish_Mathur, are you saying I should transform the data into a separate aggregate table first? That's not how the data is currently set up, but I can conceptualize that for you:

 

 

 

 201520162017minmax
Jan 151712517
Jan 251712517
Jan 351712517

 

etc. Those last two columns are what I'm trying to create, a min/max value by date. The yearly totals are currently easily retrieved. I can't figure out how to create the min/max data from what I have right now. Since they're aggregates, they can't exist as row items in my current tables.

Highlighted
Super User V
Super User V

Re: Summarize across tables with 1 to many relationship in the wrong direction

Hi,

To keep it very simple, just share data for 4-5 rows and show the actual result that you want to see in a Table format for the 4-5 sample rows of data that you share.  For now, let's not bother about the visual that you want to create - that can always be created later.


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

Re: Summarize across tables with 1 to many relationship in the wrong direction

@Ashish_Mathur  I've adjusted the file to show the data in tabular form as requested, without the min/max series. I can't get the min/max  in the BI file, that's what I'm asking for help with; a series of max/min for each date, agnostic of year. The actual result is also correct in the table in my last response for the small sample of dates provided.

Highlighted
Super User V
Super User V

Re: Summarize across tables with 1 to many relationship in the wrong direction

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

View solution in original post

Highlighted
Frequent Visitor

Re: Summarize across tables with 1 to many relationship in the wrong direction

@Ashish_Mathur  This looks great, thanks for your help! Looks like you manipulated the main data a ton as well, I'll be sure to do that on my main model.

Highlighted
Super User V
Super User V

Re: Summarize across tables with 1 to many relationship in the wrong direction

You are welcome.


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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Top Solution Authors