Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
yohay
Frequent Visitor

How to analyze two tables

I have the following tables:

 

users events:

  Event                  Date

page12016/1/14 10:22:22PM
page12016/1/14 10:22:22PM
page12016/1/14 10:22:22PM
page12016/1/14 10:22:22PM
page12016/2/14 10:22:22PM
page22016/2/14 10:22:22PM
page12016/2/14 10:22:22PM

 

user actions:

 

  Action                 Date

action12016/2/14 10:22:22PM
action22016/2/14 9:11:22PM
action12016/2/14 11:24:22PM
action12016/1/14 11:24:22PM
action12016/1/14 11:24:22PM
action12016/1/14 11:24:22PM
action22016/1/14 11:26:22PM

 

So a distinct count of the page1 per month would be:

 

 Year       Count

Feb2
Jan4

 

The sum of action1 per month would be:

 

  Year     Count

Feb2
Jan3

 

I would like to achieve the following:

 

(distinct count action1 at Feb2016) / (sum of event1 at Feb2016) => 2/2 => 1

(distinct count action1 at Jan2016) / (sum of event1 at Jan2016) => 3/4 => 0.75

 

 Year      %of actions per event

Feb1
Jan0.75

 

I want to chart that in power bi.

Any help would be appreciated.

1 ACCEPTED SOLUTION
v-huizhn-msft
Employee
Employee

Hi @yohay,

 

For your requirement, you should summarize the two tables, create the relationship between the new summarized tables. Then create calculated column to get the expected result.

I try to reproduce your scenario as follows.

Create new table using the formulas, and get the two new table shown in the following screenshot.

Table = SUMMARIZE(Table1,Table1[Month],"Page1",CALCULATE(COUNTA(Table1[Event]),FILTER(Table1,Table1[Event]="page1")),"Page2",CALCULATE(COUNTA(Table1[Event]),FILTER(Table1,Table1[Event]="page2")))

1.PNG

Table 2 = SUMMARIZE(Table2,Table2[Month],"Action1",CALCULATE(COUNTA(Table2[Action]),Table2[Action]="action1"),"Action2",CALCULATE(COUNTA(Table2[Action]),Table2[Action]="action2"))

 
2.PNG
 
Then create the relationship between the Table and Table 2.

 

3.png
 
Create calculated columns using the formulas below, and you get the expected result.


Action1 = RELATED('Table 2'[Action1])
Action2 = RELATED('Table 2'[Action2])
Percentage = 'Table'[Action1]/'Table'[Page1]

4.PNG
 
If you have any other issue, please feel free to ask.

Best Regards,
Angelia

View solution in original post

2 REPLIES 2
v-huizhn-msft
Employee
Employee

Hi @yohay,

 

For your requirement, you should summarize the two tables, create the relationship between the new summarized tables. Then create calculated column to get the expected result.

I try to reproduce your scenario as follows.

Create new table using the formulas, and get the two new table shown in the following screenshot.

Table = SUMMARIZE(Table1,Table1[Month],"Page1",CALCULATE(COUNTA(Table1[Event]),FILTER(Table1,Table1[Event]="page1")),"Page2",CALCULATE(COUNTA(Table1[Event]),FILTER(Table1,Table1[Event]="page2")))

1.PNG

Table 2 = SUMMARIZE(Table2,Table2[Month],"Action1",CALCULATE(COUNTA(Table2[Action]),Table2[Action]="action1"),"Action2",CALCULATE(COUNTA(Table2[Action]),Table2[Action]="action2"))

 
2.PNG
 
Then create the relationship between the Table and Table 2.

 

3.png
 
Create calculated columns using the formulas below, and you get the expected result.


Action1 = RELATED('Table 2'[Action1])
Action2 = RELATED('Table 2'[Action2])
Percentage = 'Table'[Action1]/'Table'[Page1]

4.PNG
 
If you have any other issue, please feel free to ask.

Best Regards,
Angelia

Thank you very much Angelia @v-huizhn-msft 

I will follow your solution!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.