Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
aabi
Helper I
Helper I

New table from existing table with calculate columns

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 :

 

UserIdSubscriptionIDSubscription TypeSubscription Status
100546546879Type 1Paid
100213546Type 1Free
101546879213Type 1Paid
101879213879Type 1Trial
1028213879Type 1Paid
102879213879Type2Paid
10223489Type2Free
10218974Type2Trial
103789Type1Trial
1031246789Type2Free
10478916546Type2Expired

 

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 :

 

UserIDCustomer Type
100Paid - Type 1
101Paid - Type 1
102Paid - Both
103Trial - Both
104Expired - 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?

 

1 ACCEPTED SOLUTION
mussaenda
Super User
Super User

Hi @aabi ,

 

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

mussaenda_0-1652251816724.png

 

hope this helps.

 

View solution in original post

2 REPLIES 2
aabi
Helper I
Helper I

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
Super User
Super User

Hi @aabi ,

 

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

mussaenda_0-1652251816724.png

 

hope this helps.

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors