cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Creating segments of customers based on multiple rows & values

Hi,

 

I have rows of invoices, so lets say for 1 customer there are 4 invoices with a text description:

 

1. Standard module 1
2. Premium module 1

3. Standard module 2

4. Premium module 2

 

I want to check whether in the current month a customer has at least one premium module. IF so, then the client is a premium client this month. So I need the check this for every client in one month.

 

I am a bit stuck because dynamic segmentation only looks at numbers, not at text. Could anyone give a hint?

2 ACCEPTED SOLUTIONS

Accepted Solutions

Hi @Hidde,

 

You can use below measure to check premium status.

 

Is premium =
SEARCH (
    "Premium",
    CONCATENATEX ( ALLSELECTED ( Table1[Module] ), [Module], "," ),
    1,
    0
)
    > 0

1.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Thanks I managed to solve it this way:

 

if(search("Premium",Table1[Modules],,0)>0,,1,,blank())

View solution in original post

7 REPLIES 7
Community Support
Community Support

HI @Hidde,

 

Can you please share some sample data? I'm not so clarify for your data structure.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

 

 So for every month and every customer the formula needs to check if there is at least one invoice with a "premium" module.

 

Thanks for your help already.

 

YearPeriodeRelatienaamInvoicenumberModuleDesired outcome
20181Client 113-104223Standard module 1Premium client
20181Client 113-104223Premium module 2Premium client
20181Client 113-104223Premium module 1Premium client
20181Client 113-104223Standard module 2Premium client
20181Client 113-104292Extra modulePremium client
20181Client 113-104292Standard module 2Premium client
20181Client 113-104368Standard module 1Premium client
20181Client 113-104368Premium module 2Premium client
20181Client 113-104368Premium module 1Premium client
20181Client 113-104368Standard module 2Premium client
20181Client 213-104223Standard module 1Standard client
20181Client 213-104223Standard module 2Standard client
20181Client 213-104292Extra moduleStandard client
20181Client 213-104292Standard module 2Standard client
20181Client 213-104368Standard module 1Standard client
20181Client 213-104368Standard module 2Standard client
20181Client 113-104223Standard module 1Premium client
20181Client 113-104223Premium module 2Premium client
20181Client 113-104223Premium module 1Premium client
20181Client 113-104223Standard module 2Premium client
20181Client 113-104292Extra modulePremium client
20181Client 113-104292Standard module 2Premium client
20181Client 113-104368Standard module 1Premium client
20181Client 113-104368Premium module 2Premium client
20181Client 113-104368Premium module 1Premium client
20181Client 113-104368Standard module 2Premium client
20181Client 213-104223Standard module 1Premium client
20181Client 213-104223Standard module 2Premium client
20181Client 213-104292Extra modulePremium client
20181Client 213-104292Standard module 2Premium client
20181Client 213-104368Standard module 1Premium client

YearPeriodCustomer_nameInvoicenumberModuleDesired outcome
20181Client 113-104223Standard module 1Premium client
20181Client 113-104223Premium module 2Premium client
20181Client 113-104223Premium module 1Premium client
20181Client 113-104223Standard module 2Premium client
20181Client 113-104292Extra modulePremium client
20181Client 113-104292Standard module 2Premium client
20181Client 113-104368Standard module 1Premium client
20181Client 113-104368Premium module 2Premium client
20181Client 113-104368Premium module 1Premium client
20181Client 113-104368Standard module 2Premium client
20181Client 213-104223Standard module 1Standard client
20181Client 213-104223Standard module 2Standard client
20181Client 213-104292Extra moduleStandard client
20181Client 213-104292Standard module 2Standard client
20181Client 213-104368Standard module 1Standard client
20181Client 213-104368Standard module 2Standard client
20182Client 113-104223Standard module 1Premium client
20182Client 113-104223Premium module 2Premium client
20182Client 113-104223Premium module 1Premium client
20182Client 113-104223Standard module 2Premium client
20182Client 113-104292Extra modulePremium client
20182Client 113-104292Standard module 2Premium client
20182Client 113-104368Standard module 1Premium client
20182Client 113-104368Premium module 2Premium client
20182Client 113-104368Premium module 1Premium client
20182Client 113-104368Standard module 2Premium client
20182Client 213-104223Standard module 1Premium client
20182Client 213-104223Standard module 2Premium client
20182Client 213-104292Extra modulePremium client
20182Client 213-104292Standard module 2Premium client
20182Client 213-104368Standard module 1Premium client
20182Client 213-104368Premium module 2Premium client

YearPeriodCustomer_nameInvoicenumberModuleDesired outcome
20181Client 113-104223Standard module 1Premium client
20181Client 113-104223Premium module 2Premium client
20181Client 113-104223Premium module 1Premium client
20181Client 113-104223Standard module 2Premium client
20181Client 113-104292Extra modulePremium client
20181Client 113-104292Standard module 2Premium client
20181Client 113-104368Standard module 1Premium client
20181Client 113-104368Premium module 2Premium client
20181Client 113-104368Premium module 1Premium client
20181Client 113-104368Standard module 2Premium client
20181Client 213-104223Standard module 1Standard client
20181Client 213-104223Standard module 2Standard client
20181Client 213-104292Extra moduleStandard client
20181Client 213-104292Standard module 2Standard client
20181Client 213-104368Standard module 1Standard client
20181Client 213-104368Standard module 2Standard client
20182Client 113-104223Standard module 1Premium client
20182Client 113-104223Premium module 2Premium client
20182Client 113-104223Premium module 1Premium client
20182Client 113-104223Standard module 2Premium client
20182Client 113-104292Extra modulePremium client
20182Client 113-104292Standard module 2Premium client
20182Client 113-104368Standard module 1Premium client
20182Client 113-104368Premium module 2Premium client
20182Client 113-104368Premium module 1Premium client
20182Client 113-104368Standard module 2Premium client
20182Client 213-104223Standard module 1Premium client
20182Client 213-104223Standard module 2Premium client
20182Client 213-104292Extra modulePremium client
20182Client 213-104292Standard module 2Premium client
20182Client 213-104368Standard module 1Premium client
20182Client 213-104368Premium module 2Premium client

https://omniaretail-my.sharepoint.com/:x:/p/hidde/ESkcr7QyCkBDug7dck9LctEBG3tbDIbxVgD1riMgsWEf5A?e=T...

 

In this file you can see the desired outcome and sample data.

 

So for every month & customer I need to check whether at least 1 Premium Module was invoiced...

Hi @Hidde,

 

You can use below measure to check premium status.

 

Is premium =
SEARCH (
    "Premium",
    CONCATENATEX ( ALLSELECTED ( Table1[Module] ), [Module], "," ),
    1,
    0
)
    > 0

1.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Thanks I managed to solve it this way:

 

if(search("Premium",Table1[Modules],,0)>0,,1,,blank())

View solution in original post

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors