New table from existing table with calculate columns

[aabi]

Hello all

I have the a dataset which contains all subscriptions statuses for all users.

I need to know for each user their status which is based on their subscription status paid/trial/free/expired and in which Paid superseeds Trial and Trial superseeds Free... Also to join the multiple types (SKUs)

The data looks as follows :

UserId	SubscriptionID	Subscription Type	Subscription Status
100	546546879	Type 1	Paid
100	213546	Type 1	Free
101	546879213	Type 1	Paid
101	879213879	Type 1	Trial
102	8213879	Type 1	Paid
102	879213879	Type2	Paid
102	23489	Type2	Free
102	18974	Type2	Trial
103	789	Type1	Trial
103	1246789	Type2	Free
104	78916546	Type2	Expired

SubscriptionID is always unique, I don't need it!

Subscription type is always type 1 or type 2. But it could add a type 3 soon (SKU type)

Subscription Status = Has priority Paid/Trial/Free/Expired (This is a calculated column in my report based on other data in that table)

Then end-result I'm looking would be :

UserID	Customer Type
100	Paid - Type 1
101	Paid - Type 1
102	Paid - Both
103	Trial - Both
104	Expired - Type 2

User 100 has 2 subscriptions.  1 paid and 1 free type 1 = user is Paid & Type 1. I don't care about the free

User 101 has 2 subscriptions. 1 paid and 1 trial type 1 = user is Paid & Type 1. I don't care about the trial

User 102 has 4 subscriptions. 2 are paid and its both type 1 and type 2= Paid with Type 1 and Type 2. Again trials are ignored

User 103 has 2 subscriptions. 1 trial and 1 free both type 1 and type 2 = Trial with Both

So effectively the Customer Type = Highest Subscriptions status & Subscriptions Types (multiple 1 +2).

Is it possible to do all this in power BI somehow?

Any ideas anyone how to solve this problem?

[aabi]

Hi @mussaenda 

Thank you for this! It does work and it seems as a nice solution!
I will try to understand it a bit more as I'm not that good M-Query.

I have actually managed to do this on my own using DAX.
I made a new table with all users (dinstict) and then used 2 calculate columns with SWITCH(true) to mark each user Paid/Trial/Free/Expired, then mark each user as Type 1, Type 2 or Both.
Then a third column assesing for all possible combinations of the 2 other Switch columns!

But still I appreciate your time for making a solution!

[mussaenda · Accepted solution]

Hi @aabi ,

This may not be the best solution but it achieved what you need.

hope this helps.