Forum Discussion
Selecting single value from multiple rows
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
Hi djheathy,
According to your needs, I created the following content for your reference, I hope it can help you:
- Create a table for determining the level of service by using value 1,2,3:
- 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/EXxfieGQruBAn6Sq8bokdkIB4LsIjZJoXwFwQOqPbA9z9g?e=rHndI1
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon
4 Replies
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
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!
Hi djheathy,
According to your needs, I created the following content for your reference, I hope it can help you:
- Create a table for determining the level of service by using value 1,2,3:
- 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/EXxfieGQruBAn6Sq8bokdkIB4LsIjZJoXwFwQOqPbA9z9g?e=rHndI1
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon
