Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 ID | Value | Start Date | End Date |
345 | 5 | 1/1/2014 | 6/1/2015 |
567 | 75 | 1/1/2015 | 6/1/2016 |
Detail:
Unique ID | Date |
345 | 1/1/2014 |
345 | 1/2/2014 |
345 | 1/3/2014 |
345 | 1/4/2014 |
etc | etc |
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi @Anonymous ,
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.
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.
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.
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:
2015 | 2016 | 2017 | min | max | |
Jan 1 | 5 | 17 | 12 | 5 | 17 |
Jan 2 | 5 | 17 | 12 | 5 | 17 |
Jan 3 | 5 | 17 | 12 | 5 | 17 |
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.
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.
@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.
Hi,
You may download my PBI file from here.
Hope this helps.
@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.
You are welcome.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |