Table Relationship

[faisayed]

Hi

I have 2 tables, Table A comes from database, and Table B comes from Google Analytics. Table A has the total number of registration Country, Device and Source wise, and Table B has total number of users Country, Device and Source wise. I have a line graph that shows the last 7 days  rolling avearge registration rate, formula for which is "total number of registraton" (From Table A) divide with the "total number of users" (From Table B).

I want to add 3 more metrics to my dashboard

1. Registration Source wise

2. Registration Country wise

3. Registration Device wise.

I want the formula to work in such a way that it divides the total number of particular source e:g google with total number of registrations done through google and so on. and this should apply to Device and source as well.

What is the best way to do this. What all relationship should be created to get the end result..

[amitchandak · Accepted solution]

@faisayed , You need to create common country, source, use, and date table, and then you can create rolling measure using the date table

Rolling 7 = CALCULATE(Sum(Table1[Reg Count]) ,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-7,DAY))

Rolling 7 user= CALCULATE(Sum(Table2[User Count]) ,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-7,DAY))

refer

https://amitchandak.medium.com/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solutio...

[faisayed]

Thank You @amitchandak for quick response and excellent workaround for my problem. 

I got the outcome i was looking for.👍