Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have 3 tables
1. A customer table that contains details of all customers
2. A Subscription table that contains ONLY customer on our subscription plan.
3. A transaction table that contains all customer transactions.
The customer ID links all three tables.
The subscription is renewed every month through an auto debit on the customer's account and this transaction is registered both on the transaction table and the subscription table.
The team would like to track each new subscriber to the bundle daily. We have multiple renewals daily but we to count new subscribers to the subscription ONLY.
We may have a new customer that is transacting but isn't subscribed to the bundle yet. What i want to do is count each first time subscriber to the bundle irrespective of when you became a customer. So each time a subscription transaction hits the transaction table it checks to see if that number is already subscribed, If yes; It doesnt count, If no it counts.
Tired DISTINCTCOUNT but dosnt count correctly.
Hi @Anonymous ,
Please give the example data of the three tables so that we can help you quickly and accurately.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Share a simple dataset and show the expected result very clearly.
@Anonymous
I just created two simple tables.
Subscription
Transaction
Then you create measures
trans not sub =
VAR tbl= SUMMARIZE(FILTER('transaction','transaction'[customer]<>RELATED(subscription[customer])),'transaction'[customer])
return COUNTROWS(tbl)
trans and sub =
VAR tbl= SUMMARIZE(FILTER('transaction','transaction'[customer]=RELATED(subscription[customer])),'transaction'[customer])
return COUNTROWS(tbl)
Hope this is helpful.
Proud to be a Super User!
Which fields do you have in your tables
?
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
i do not understand the requirement fully, let me be upfront about this, however, you can use the RELATED function to determine if a customer in the transaction table has a subscription for that specific entry when you do the analysis. this will give you the 0, 1 scenario. when you create the subscription based on your tables all the entries will be true for that customer.
Subscribed Column = RELATED('SubscriptionsTable'[BundleColum]')
the requirement however almost seems like it is operational at the transaction level rather than analysis (unless you wish to use the power bi service), then again i did mention i do not fully understand. - just a thought.
below the red are customers in the transaction table but no bundles assigned in subscriptions table
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |