Count matching values in different queries

[flipfis]

Situation

We offer two different plans to our customers. I imported the data from this via a web query in JSON. The two subscriptions both have their own data table.

Problem

We want to calculate which customers have both subscriptions by searching based on a customer id. So basically we would like to see how many rows have the same customer id in both datasheets to calculate the number of customers who have both plans.

[Anonymous · Accepted solution]

Hi  @flipfis ,

I created the data:

Subscriptions1:

Subscriptions2:

Here are the steps you can follow：

1. Create calculated column

Flag1 =
var _plan=SELECTCOLUMNS(FILTER(ALL(Subscriptions1),[Customer]=EARLIER([Customer])),"1",[Plan])
return
IF("PlanA" in _plan&&"PlanB" in _plan,1,0)

Flag2 =
var _plan=SELECTCOLUMNS(FILTER(ALL('Subscriptions2'),[Customer]=EARLIER([Customer])),"1",[Plan])
return
IF("PlanA" in _plan&&"PlanB" in _plan,1,0)

2. Create measure.

Flag all =
var _flag1=MAX('Subscriptions1'[Flag1])
var _flag2=CALCULATE(MAX('Subscriptions2'[Flag2]),FILTER(ALL('Subscriptions2'),[Customer]=MAX('Subscriptions1'[Customer])))
return
IF(_flag1=1&&_flag1=_flag2,1,0)

3. Put measure[Flag all] into Filter, set is=1, and Apply filter.

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[flipfis]

@amitchandak Thank you very much for your quick reply! But I'm not quite sure what to do with your solution. I'm quite new to BI, sorry. I don't know where to create these things. It would be great if you could explain it a little bit more 🙂 

[amitchandak]

@flipfis , You need to have a common customer table, assumed PlanAData and PlanBData are your tables with the customer as one of the columns

New Table Customer 

Customer = distinct(Union(distinct(PlanAData[Customer),distinct(PlanBData[Customer)))

Join the new table to another two tables on Customer 

Create three measure as I suggested in last post, thrid measure is what need.

[flipfis]

@amitchandak So I now created a new table and used your forumula from the video:

BothMemberships = DISTINCT(UNION(ALL('PlanA'[customer_id]),ALL('PlanB'[customer_id])) )

That works like a charm, but I really would like to apply a filter inside that formula. I want to exclude the memberships that have the status: "cancelled" and "pending". So I only want to get the active memberships and THEN count the duplicates. I'm still not quite sure how to count the duplicates via DAX. 

[flipfis]

When I try to create a new table and enter the measure it's giving me the following error: The expression specified in the query is not a valid table expression.

PlanA = count('MembersPlanA'[customer_id])

[amitchandak]

@flipfis , You need a common customer table

You need have measure from both tables like .

PlanA = count(PlanAData[Customer])
PlanB = count(PlanBData[Customer])

Then create a measure and display it with customer from common customer table

Countx(filter(Values(Customer[Customer]), not(isblank([PlanA])) && not(isblank([PlanB]))), [Customer])

Common Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19