Forum Discussion
Merged Tables vs Related Tables
Hi,
I need help to understand an issue with related tables vs merged queries.
I am performing a report in which I need to get several calculations from 2 distinct tables.
My main table have all tickets (different statuses; closed, created, assigned, etc) and the secondary table only have tickets that were/ are in a specific status (pending customer).
I have a relationship: secondary table --> main table of Many to One (both directions). This way I can create calculated columns in the main table just like in the secondary table. The problem that I am facing now is that I would need to calculate the days between two dates (present in the secondary table: "start time" and "today"), and include as well the milestone status (calculated column in the main table: "stopped" or "not provided").
I need to mention that the main table only has unique ticket numbers, but the secondary table has duplicates. If a ticket during its case age was 3 times in pending customer state, then it will have 3 rows for each time it has started and stopped. And if a case was already pending customer last month, and is currently in pending customer, it will have a row with start and stop time, and another row with start time, but no stop time for the current pending customer state.
Is there any way I can create this kind of formula using several columns from 2 distinct tables (with relationship between them)?
***************
And now I would like to present another scenario, exactly the same tables, but where I merged the queries so I could get all information into one single table. I thought that this way it would be easier to create the calculated columns that I need.
I merged the queries as new, selected the Left Outer Join, so I could have all data from the main table, and the matching data from the secondary.
I even created my calculation in power query, to get the days between two dates for the tickets that are currently in pending customer status (start time and today).
However I am facing another issue. When I load the merged query into Power bi desktop, all data is showed, except if I create a table with columns from both tables, it will be automatically filtered and I will only get the tickets from the secondary table (tickets that were/ are in pending customer status). I tested with different columns from the secondary table, if I add the calculated column I will only have tickets in pending customer status, but if I remove that column and add the case_id columns, I will get all tickets.
If this is a merged query, shouldn't the merged table show all data? I also tried to change the left outer joint to full outer, but the behavior is the same. As soon as I add the calculated field (obtained through columns from the secondary table) the case list is filtered.
This is the simplest table with just the ticket nr and the case status. As you can see these 9 tickets are in different status. These columns are from the main table.
| Ticket Nr | Ticket Status |
| 1 | Pending Customer |
| 2 | Solution Provided |
| 3 | In Progress |
| 4 | Solution Provided |
| 5 | Pending Customer |
| 6 | Solution Provided |
| 7 | In Progress |
| 8 | Pending Customer |
| 9 | In Progress |
And here I added the calculated column to get the days between the start time and today, and it filtered automatically the tickets and is only showing the ones in pending customer, or that have a "start time" filled. The formula itself is working, because the result is correct. The problem is that I need this calculation in my ticket list, but I cannot use it if it filters out the rest of the tickets.
| Ticket Nr | Ticket Status | Days in Pending Customer - current date |
| 1 | Pending Customer | 17,81 |
| 5 | Pending Customer | 19,00 |
| 8 | Pending Customer | 18,00 |
| 9 | In Progress | 8,26 |
Is this supposed to happen?
Perhaps it is normal and I am not seeing this the right way… I really need help, I spent at least 3 weeks on this and cannot go further. Every time I think I overcome an issue, another one gets on the way.
And I apologize if I did not explained myself very well… it is difficult for me to understand the logic of all this and explain it in English even more…
I hope someone can help me and tell me what should I do…
3 Replies
The English is very good. However it would be better to include data and pictures to explain.
Part1 - I'm not exactly sure what you want
Part 2 - It looks like you have added 'Days in Pending Customer - current date' to the visual and this creates an implicit measure. Powerbi then filters out the blank values.
If you want to use the 'Show items with no data' option (from the dropdown in the Values well), you should get the other tickets in the visual
Hi HotChilli
Thanks for your reply.
1st Part - I was trying to know if there's a way to create a calculated column with fields from different tables.
For instance, I have a calculated column to obtain the milestone status in my first table. And in the second table I have a calculated column to get the number of days that a ticket have on pending customer status.
And I would need to calculate the number of days left until the milestone target, summarizing with the number of days in pending customer, for the tickets in pending customer status (in this situation the milestone is stopped, and so the number of days that is stopped will be added and the target date is postponed).
It would be something like this:
Days to SLA =
IF('Merged_Table'[Milestone_Status] = "Not provided", DATEDIFF('Merged_Table'[Today],'Merged_Table'[Target_Date],DAY),
IF('Merged_Table'[Milestone_Status] = "Stopped", DATEDIFF('Merged_Table'[Today],'Merged_Table'[Target_Date],DAY) + [Ongoing Pending Customer Time]))
However I cannot perform this DAX, because [Milestone_Status] has been created in the first table, and the [Ongoing Pending Customer Time] on the second table. Even if I have relationship between them of Many-to-One. The second table has duplicates, btw.
2nd Part - I was not able to test what you have suggested, but I was discussing and testing a lot of options and was able to find a solution (at least until a new issue comes ahahahah).
Don't know exactly the order of the process, because I tested so many things that I can't remember everything I did. But basically I created a column to define values as 1 or 0, and then I created the column to get the number of days for pending customer time. I still had to solve the duplicates, but defined them as null and could exclude them from the table. For now it is working. Hope I don't get more issues of this kind!
Thank you for your help 😉
