Help creating a relation, not finding a way to make it work.

[bolabuga]

Hello everyone,

I have the following scenario:

Link for my .pbix sample. https://drive.google.com/open?id=0B8Aq8DhGApJqa3VkNXhsQm1BMEk (it can be downloaded using a down arrow on the up right side of the screen.)

1- city table

2- formated city table to make a relation with service table

3-service table

4- The relation created

I have done 2 metrics to use on this sample:

SERVICE COUNT CITY A = COUNTX(FILTER('SERVICE TABLE';'SERVICE TABLE'[CITY]="A");'SERVICE TABLE'[SERVICE number])

% SERVICE BY TOTAL CLIENTS CITY A = DIVIDE('SERVICE TABLE'[SERVICE COUNT CITY A];SUM('FORMATED CITY TABLE'[TOTAL CLIENT CITY A]))

For the problem:

I need to divide the number of services executed in a city by the number total of clients in that city, the way i have things here now, i will have to make a diferent metric to count the services in each city, and then create a % metric for each count metric. With this i will need 12 metrics in total just for this scenario. 

For the Question:

Is there a way to make a relation between the city table and client table?? If that was possible i would need just 1 metric to count all services and another SUM metric for the total clients, allowing me to use divide([count services];[total clients]). Then i could use report view filters or slicers to see the results by reference or city. 

obs: is there a way to upload here on forums my 1mb pbix sample?? that would make things a lot easier.

obs2: i just added a link on the start of the post.

[Vvelarde]

@bolabuga

You can upload to OneDrive, DropBox or another similar and share the link.

[bolabuga]

@Vvelarde thks, i tried both suggestions from my work, but they are blocked, in truth every place i tried upload the file was blocked. Im doing it from home now. I used google drive.

[v-huizhn-msft · Accepted solution]

Hi @bolabuga,

>> Is there a way to make a relation between the city table and client table??

client table refers to Service Table, right? If it is, we are unable to create relationship between them. In order to create a relationship in between these two columns in Power Pivot, at least one side of the relationship must be tied to a column that is unique in the table. There is no many to many relation in Power BI model.

>> With this i will need 12 metrics in total just for this scenario.

You can create multiple measure and add all the measure to one matrix. Like the following screenshot, you can create C,D,E,F and add them at one time.

If you have any question, please feel free to ask.

Best Regards,
Angelia

[bolabuga]

@v-huizhn-msft thks for your reply, i will make it that way then :).