Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have two tables.
Table 1 Invoice:
Table 2 Reminders:
Now i want to create a visual for reminders per year per period. It is the red ones i am having issues with:
Example for one year, (I have data for many years)
Year | Period | Reminder sum | Total invoiced same period | Reminder sum percentage of total invoices |
2022 | P1 | 900 | 3.600 | 25% |
2022 | P2 | 200 | 400 | 50% |
I have a Date table, so Reminder date and invoice date have a relationship.
I am able to create the first three columns, but for the next columns, i am really struggling.
I have tried to write a measure:
TotalInvoiced=CALCULATE(SUM(tbl_Invoice[Amount]),FILTER(tbl_Invoice,tbl_invoice[Period]=
But i am not able to set tbl_invoice[period] =tbl_Reminder[Period].
Tbl_Reminder doesn't show up as a valid choice. If i hardcode it, it says (translated from Norwegian):
"Cant determine single value for the column 'Period', this can happen when it contains too many values without aggregation, for example min, max, count or sum to get a single result."
Solved! Go to Solution.
Hi. This is because u are trying to filter it based on tbl_invoice.period while this is connected to Reminders.
It cannot filter on multiple values in this way.
Are the 2 tables connected with a relation??
No they are not related with any relation.
I am not entirely sure how to relate them, as the invoicenumber only appears once in tbl_Invoice, but it can appear multiple times in tbl_Reminder.
Period is also a many to many, and i am not sure what to choose if am to relate these?
Can u show us the ERD? cross out any sensitive information and replace with dummy data.
Termin = Period
Fakturadato= Invoice Date
PurreDato = Dunning/reminder date
Following your hint about relationship between these two tables, I created a dummy table, called tbl_Termin (period), and creating a relationship between tbl-Purring(reminder) and tbl_Faktura(invoice), i managet to get the correct data.
Perfect, I was gonna look at the ERD and check if there was a many:many relation needing a middle dummy table with both unique values from either table. Good that u figured it out. Tag me in any other posts if i can help i will.
Hi. This is because u are trying to filter it based on tbl_invoice.period while this is connected to Reminders.
It cannot filter on multiple values in this way.
Are the 2 tables connected with a relation??
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |