Extract Data from another table using another table

[Anonymous]

Hi Guys,

I have an 'AppDate' table that looks like below:

AppDate
CostCenter	AppId	SubDate
11	1	1/1/2019
22	2	1/5/2019
33	null	null
44	3	2/5/2019
55	4	2/27/2019
66	6	5/3/2019
77	10	5/5/2019
88	18	5/23/2019

There is another table that has all detailed info of the Cost centers ( they may or may not be present in the above AppDate table):

CostCenterInfo
CostCenter	Name	Home State
11	AA	California
22	BB	Florida
33	CC	Texas
44	DD	Florida
55	EE	California
66	KK	Arizona
77	YY	California
88	NN	Arizona
99	GG	Texas
100	TT	Texas

Using a date filter, I need to get the list of all Cost centers that do not have any application associated to them. 

I need two things:

1. KPI : Count of Cost Centers with no Apps

2. Table of all Branches that have no Apps 

In the above case, if the end user selects date range of 'Feb 2019',

1. Count of Cost Centers with No Apps  will be '8'. (Since there are only two brancehs in Feb'19 that returned apps in this month and there are a total of 10 branches inthe second table ( CostCenterInfo table).

2. The table should all info for the above 8 branches that did not return any apps in that month.

It seems easy in SQL but, I am unable to figure out how to do it in Power Bi.

Please help!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Thanks @Ashish_Mathur . This is really helpful..

Could you please help me understand the logic you used for the 'No Apps' metric.

[Ashish_Mathur]

Hi,

I'd request you to please read up on the functions used in that formula on the MSDN site.  If you are still unclear, then post here and i will clarify your doubts.