cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted

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

Highlighted

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
Highlighted
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.
Highlighted

 

 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
Highlighted

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
Highlighted

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
Highlighted

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...

Highlighted

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

Highlighted

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors