Selecting single value from multiple rows

[djheathy]

I've created an example to illustrate the challenge that I'm facing, but basically, I want to show active subscribers over time (I'm ok with this bit) and total subscription value. However, I only want to see them categorised ONCE against their total subscription value depending on what Services they subscribe to.

E.G. if they subscribe to a RED service plus any other service then categorise them as RED (for the period the RED service was active), if their is no, RED, but a BLUE then categorise them as BLUE etc. 

It's pretty straight forward to each of their individual services, but I only want them categorised once, based on some Case logic. I can allocate each category a rank using the SWITCH function, and try creating a calculation selecting max rank, but I'm still seeing counting in both services due to the 1:n nature of the relationship

[amitchandak]

@djheathy , What is the final output you want.

"A" should be blue? or you want to remove the overlap. Can you share sample data in a table format? Also sample output

[djheathy]

Hi @amitchandak 

Sure, so in a table showing 'current' subscribers (I only have two in the example I mocked-up), I would see something like:

Service	Number of Subscribers
Red	1
Blue	1
Green	0
None	0

Subscriber C would be counted once as having a 'Red' serive and subscriber A would be counted once as having a 'blue' service. 

Even though subscriber C also has an active blue subscription, I don't want to count them there, as I've already counted them once in a higher ranking category.

I hope this makes sense. I could use a calculated column to capture this primary category, but because I need to see this over time, it needs to be more dynamic than that. 

Thank you for responding!

[v-deddai1-msft · Accepted solution]

Hi @djheathy,

According to your needs, I created the following content for your reference, I hope it can help you:

1. Create a table for determining the level of service by using value 1,2,3:

1. Create a column for calculating the value of the highest level of service (active subscriber)subscribed by the current subscriber:

Column = CALCULATE(MAX(Service[Column1]), FILTER(ALL(Subscriber), Subscriber[Subscriber] = EARLIER(Subscriber[Subscriber])&&Subscriber[to] = BLANK()))

3.Create a column for calculating Whether the level of the current service is higher than the highest level of service subscribed by the current subscriber, if it is return 1, otherwise return 0:

Column 2 = IF(Subscriber[Column] = BLANK(),0,IF(RELATED(Service[Column1])<Subscriber[Column],0,1))

4. Create a measure for calculate active subscriber for each service:

Measure = var a = CALCULATE(COUNTROWS(Subscriber),Subscriber[Column 2] = 1) return IF(a = 0,0,a)

Please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EXxfieGQruBAn6Sq8b...

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

Best Regards,

Dedmon

[djheathy]

@amitchandak 

Maybe I should be trying to create 4 separate measures and filter down each measure using the CONTAINS function?