Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi guys
I have following issues and as new user to PowerBI tried to look for different solutions but neither quite worked / matches my issue (probably because data are located in different tables).
So I have bookings (tbl_bookings) with Start and End date. The Amount sits in different table (tbl_amount). I need to spread the amount across differnet time periods (year months, calendar year, financial year, week,etc.) based on the start and end date. And then summarize, filter or view this again based on different columns from different tables.
What i did so far is that I created Date dimensions with date formats I need. I thought of using the Role Playing approach to come around the one active joint limitations. Any ideas if there may be better way?
I tried to create Measure BookingPerMonths but I get following error code (altough there is 1:1 and start,finish date filled for each booking).
BookingsPerMonth = Calculate(SUM(tbl_amount[cur_amount]), FILTER(tbl_amount,COUNTROWS(FILTER(VALUES(DateDim[FulldateAlternateKey]),tbl_booking[dte_start] <= DateDim[FulldateAlternateKey] && tbl_booking[dte_finish] >= DateDim[FulldateAlternateKey] )) > 0))
Error code: A single value for column 'dte_start' in table 'tbl_booking' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
I would love to get something like this matrix as intermediate product which I will group, filter, view again based on eg. customer, date when booking was made, sales rep etc.
Booking | Jul 17 | Aug 17 | Sep 17 | Oct 17 | Nov 17 | Dec 17 | Jan 18 | Feb 18 | Mar 18 | Apr 18 | May 18 | Jun 18 | Jul 18 | Aug 18 | Sep 18 | Oct 18 | Nov 18 | Dec 18 |
26094 | 11,214 | 1,286 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
26231 | 31,086 | 23,314 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
23107 | 5,020 | 12,724 | 12,314 | 410 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
24899 | 670 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
26511 | 0 | 0 | 0 | 10,045 | 10,045 | 10,380 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
26512 | 0 | 0 | 0 | 0 | 0 | 0 | 10,060 | 9,086 | 10,060 | 325 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
27864 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 23,074 | 25,382 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
These are my tables with tbl_booking and tbl_amount are joint using table tbl_amount_booking ( uid_booking_amount and then uid_r_booking_amount on uid amount as key) all of these are 1:1
Solved! Go to Solution.
Hi Xiaoxin, @v-shex-msft
a big thanks for the solution! It works 🙂
just to confirm that I really understand your solution, the intermediate table you created actually gives me list of every date when some of the camapign was happening along with id of every campaign that was happening on that particular date.
All this to overcome inability of PowerBi to deal with 1:1, many:may relationships?
Thank you!!
Alex
Hi,
I have solved a similar question here - Distribute projected revenue annually.
Hope this helps.
HI @AlexMos,
After research on your formula, I found you are try to direct use tbl_booking columns which not be defined. I think it is impossible to direct use undefined and un-summarized columns as filter conditions at measure formula .
Can you please share a pbix file for test?
Regards,
Xiaoxin Sheng
Hi @v-shex-msft
the file has bit truncated data and the tables are missing "tbl_" in name for ease however everything else same.
thank you
https://1drv.ms/u/s!Ah0PpfTHcxcbg0rw6Bq8Bv9M2lts
Hi @AlexMos,
After research on your sample file, you can refer to below steps to get summary booking amount.
Steps:
1. Use booking table to create a 'booking date range' table to expand all booking uid date range.
booking date range = VAR _calendar = CALENDAR ( MIN ( booking[dte_start] ), MAX ( booking[dte_finish] ) ) RETURN SELECTCOLUMNS ( FILTER ( CROSSJOIN ( booking, _calendar ), booking[dte_start] <= [Date] && booking[dte_finish] >= [Date] ), "uid_booking", [uid_booking], "Date", [Date] )
2. Remove relationship between booking table and Datedim table.
3. 'Use booking date range' table to link booking table and Datedim table.(uid booking to booking table, date to datedime)
4. Use booking[uid booking], datedim[FulldateAlternateKey], amount[cur_amount] to create matrix visual.
Notice:
1. booking table store two date column to describe date range, they only means startdate and end date, so you can't direct use one of them to link calendar table.(your relationship not suitable for your requirement)
2. It seems like your calendar table not exist date range of 2014, so these summarized result are stored in blank column group.
3. I attach modified sample file at below.
Regards,
Xiaoxin Sheng
Hi Xiaoxin, @v-shex-msft
a big thanks for the solution! It works 🙂
just to confirm that I really understand your solution, the intermediate table you created actually gives me list of every date when some of the camapign was happening along with id of every campaign that was happening on that particular date.
All this to overcome inability of PowerBi to deal with 1:1, many:may relationships?
Thank you!!
Alex
Hi @v-shex-msft
I tried to apply this apply in my original project and it doesnt work (even tough it worked in the sample). I strated from scratch connected just tables I had in sample, keep connection you mention and it still wont work.
in matrix it will always show sum for all periods irrespective what I choose for Columns.
any idea?
Thank you!
Hi @AlexMos,
Can please share us a sample pbix file to test. I think it will be help.
Regards,
Xiaoxin Sheng
Hi @v-shex-msft,
I figured out why your solution is not working. There is another table(s) that generate revenue as cur_amount in table Amount.
Unfortunately I havent found table with relevant bookings (or rather start and end dates for these amounts). Because the database has several tens of tables and is has large volume od data it doesnt seem practical to make sample pbix untill I find the relevant tables responsible for all the amounts in Amount table.
however is there way how to bump up your solution - booking data range table with data from another tables?
booking date range = VAR _calendar = CALENDAR ( MIN ( booking[dte_start] ), MAX ( booking[dte_finish] ) ) RETURN SELECTCOLUMNS ( FILTER ( CROSSJOIN ( booking, _calendar ), booking[dte_start] <= [Date] && booking[dte_finish] >= [Date] ), "uid_booking", [uid_booking], "Date", [Date] )
User | Count |
---|---|
117 | |
74 | |
62 | |
50 | |
46 |
User | Count |
---|---|
174 | |
125 | |
60 | |
60 | |
57 |