Data Modelling question

[Anonymous]

Hi,

I might need some insight on how to model the next analysis.
I have two tables: "Reservations" and "Dealers"
Each table has Latitude and Longitude coordinates and I would like to plot both tables on a map visual.

Since the Reservations table has quite alot of rows I would like to be able to filter this table on date, but without losing the Dealers data (this table doesn't have a date column)

What I got so far:

I made a reference (helper) table of "Reservations" and "Dealers" and selected only the columns which I needed. (Code, Latitude, Longitude and Index) + a custom column which indicates which Type of record it is (either Reservation or Dealer)
I appended both tables to a new table called "ReservationDealerCombo"

With this new table I have created a relationship with the actual tables "Reservations" and "Dealers" (not the reference tables)
Relationships look like this:

This is the result:

As you can see the coordinates are plotted correctly, but due to the large amount of "Reservation" data I bearly see the "Dealer" circles. (and I have the impression that if there are too much coordinates to plot he doesn't show certain points, can anyone confirm this?)
So I though, let me filter the reservations by selecting a year & month, but as soon as I do that the "Dealer" type dissapears.
(Left: filter on 2021 and February || Right: an overview of where the dealers are (in a seperate tab )

Is there a way where I can still plot both while selecting a year/month value for the reservations?

Kind Regards,

Kevin

[Anonymous]

Hi @Anonymous,

Can please share some dummy data with a similar data structure with expected results to test?

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

[Anonymous]

Hi, 
Thanks for answering thus far.
Here is a view of what the data looks like.

Dealer	Reservation

As mentioned in my first post, I have to plot this data on a map visual.
The part with which I'm struggling is when the user would select a Year.
Only reservations have a date column and dealers don't, if your user would select a year this would filter out the Dealer data and this is not what I would like to achieve.

I would like to show the dealers always and based on the selection on date it need filter out the reservations.
In Power BI I have already combined the 2 tables, but I can't figure out how I can write a measure which does this.

Below are the results that I expect.
I have achieved this by creating 2 different measure (for the left table visual), but since map visuals don't allow 2 series, I need to solve this in 1 single measure.

 

 

KR,

Kevin

[lbendlin]

Have you tried changing the plot order so that the dealers are rendered on top of the reservations?

[Anonymous]

Hi @lbendlin , how do you do this if you are only using 1 measure ?
I can see how you do this with using 2, but not with one. Is this an option in the map visual?

[lbendlin]

Please provide sample data in usable format (not as a picture).