Forum Discussion
Selecting single value from multiple rows
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
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