Forum Discussion
Count matching values in different queries
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.
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.
6 Replies
- amitchandak
Super User
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
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.
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])
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.
