Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
Hi @Hidde,
You can use below measure to check premium status.
Is premium = SEARCH ( "Premium", CONCATENATEX ( ALLSELECTED ( Table1[Module] ), [Module], "," ), 1, 0 ) > 0
Regards,
Xiaoxin Sheng
Thanks I managed to solve it this way:
if(search("Premium",Table1[Modules],,0)>0,,1,,blank())
HI @Hidde,
Can you please share some sample data? I'm not so clarify for your data structure.
Regards,
Xiaoxin Sheng
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
Regards,
Xiaoxin Sheng
Thanks I managed to solve it this way:
if(search("Premium",Table1[Modules],,0)>0,,1,,blank())
Year | Period | Customer_name | Invoicenumber | Module | Desired outcome |
2018 | 1 | Client 1 | 13-104223 | Standard module 1 | Premium client |
2018 | 1 | Client 1 | 13-104223 | Premium module 2 | Premium client |
2018 | 1 | Client 1 | 13-104223 | Premium module 1 | Premium client |
2018 | 1 | Client 1 | 13-104223 | Standard module 2 | Premium client |
2018 | 1 | Client 1 | 13-104292 | Extra module | Premium client |
2018 | 1 | Client 1 | 13-104292 | Standard module 2 | Premium client |
2018 | 1 | Client 1 | 13-104368 | Standard module 1 | Premium client |
2018 | 1 | Client 1 | 13-104368 | Premium module 2 | Premium client |
2018 | 1 | Client 1 | 13-104368 | Premium module 1 | Premium client |
2018 | 1 | Client 1 | 13-104368 | Standard module 2 | Premium client |
2018 | 1 | Client 2 | 13-104223 | Standard module 1 | Standard client |
2018 | 1 | Client 2 | 13-104223 | Standard module 2 | Standard client |
2018 | 1 | Client 2 | 13-104292 | Extra module | Standard client |
2018 | 1 | Client 2 | 13-104292 | Standard module 2 | Standard client |
2018 | 1 | Client 2 | 13-104368 | Standard module 1 | Standard client |
2018 | 1 | Client 2 | 13-104368 | Standard module 2 | Standard client |
2018 | 2 | Client 1 | 13-104223 | Standard module 1 | Premium client |
2018 | 2 | Client 1 | 13-104223 | Premium module 2 | Premium client |
2018 | 2 | Client 1 | 13-104223 | Premium module 1 | Premium client |
2018 | 2 | Client 1 | 13-104223 | Standard module 2 | Premium client |
2018 | 2 | Client 1 | 13-104292 | Extra module | Premium client |
2018 | 2 | Client 1 | 13-104292 | Standard module 2 | Premium client |
2018 | 2 | Client 1 | 13-104368 | Standard module 1 | Premium client |
2018 | 2 | Client 1 | 13-104368 | Premium module 2 | Premium client |
2018 | 2 | Client 1 | 13-104368 | Premium module 1 | Premium client |
2018 | 2 | Client 1 | 13-104368 | Standard module 2 | Premium client |
2018 | 2 | Client 2 | 13-104223 | Standard module 1 | Premium client |
2018 | 2 | Client 2 | 13-104223 | Standard module 2 | Premium client |
2018 | 2 | Client 2 | 13-104292 | Extra module | Premium client |
2018 | 2 | Client 2 | 13-104292 | Standard module 2 | Premium client |
2018 | 2 | Client 2 | 13-104368 | Standard module 1 | Premium client |
2018 | 2 | Client 2 | 13-104368 | Premium module 2 | Premium client |
Year | Period | Customer_name | Invoicenumber | Module | Desired outcome |
2018 | 1 | Client 1 | 13-104223 | Standard module 1 | Premium client |
2018 | 1 | Client 1 | 13-104223 | Premium module 2 | Premium client |
2018 | 1 | Client 1 | 13-104223 | Premium module 1 | Premium client |
2018 | 1 | Client 1 | 13-104223 | Standard module 2 | Premium client |
2018 | 1 | Client 1 | 13-104292 | Extra module | Premium client |
2018 | 1 | Client 1 | 13-104292 | Standard module 2 | Premium client |
2018 | 1 | Client 1 | 13-104368 | Standard module 1 | Premium client |
2018 | 1 | Client 1 | 13-104368 | Premium module 2 | Premium client |
2018 | 1 | Client 1 | 13-104368 | Premium module 1 | Premium client |
2018 | 1 | Client 1 | 13-104368 | Standard module 2 | Premium client |
2018 | 1 | Client 2 | 13-104223 | Standard module 1 | Standard client |
2018 | 1 | Client 2 | 13-104223 | Standard module 2 | Standard client |
2018 | 1 | Client 2 | 13-104292 | Extra module | Standard client |
2018 | 1 | Client 2 | 13-104292 | Standard module 2 | Standard client |
2018 | 1 | Client 2 | 13-104368 | Standard module 1 | Standard client |
2018 | 1 | Client 2 | 13-104368 | Standard module 2 | Standard client |
2018 | 2 | Client 1 | 13-104223 | Standard module 1 | Premium client |
2018 | 2 | Client 1 | 13-104223 | Premium module 2 | Premium client |
2018 | 2 | Client 1 | 13-104223 | Premium module 1 | Premium client |
2018 | 2 | Client 1 | 13-104223 | Standard module 2 | Premium client |
2018 | 2 | Client 1 | 13-104292 | Extra module | Premium client |
2018 | 2 | Client 1 | 13-104292 | Standard module 2 | Premium client |
2018 | 2 | Client 1 | 13-104368 | Standard module 1 | Premium client |
2018 | 2 | Client 1 | 13-104368 | Premium module 2 | Premium client |
2018 | 2 | Client 1 | 13-104368 | Premium module 1 | Premium client |
2018 | 2 | Client 1 | 13-104368 | Standard module 2 | Premium client |
2018 | 2 | Client 2 | 13-104223 | Standard module 1 | Premium client |
2018 | 2 | Client 2 | 13-104223 | Standard module 2 | Premium client |
2018 | 2 | Client 2 | 13-104292 | Extra module | Premium client |
2018 | 2 | Client 2 | 13-104292 | Standard module 2 | Premium client |
2018 | 2 | Client 2 | 13-104368 | Standard module 1 | Premium client |
2018 | 2 | Client 2 | 13-104368 | Premium module 2 | Premium client |
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.
Year | Periode | Relatienaam | Invoicenumber | Module | Desired outcome |
2018 | 1 | Client 1 | 13-104223 | Standard module 1 | Premium client |
2018 | 1 | Client 1 | 13-104223 | Premium module 2 | Premium client |
2018 | 1 | Client 1 | 13-104223 | Premium module 1 | Premium client |
2018 | 1 | Client 1 | 13-104223 | Standard module 2 | Premium client |
2018 | 1 | Client 1 | 13-104292 | Extra module | Premium client |
2018 | 1 | Client 1 | 13-104292 | Standard module 2 | Premium client |
2018 | 1 | Client 1 | 13-104368 | Standard module 1 | Premium client |
2018 | 1 | Client 1 | 13-104368 | Premium module 2 | Premium client |
2018 | 1 | Client 1 | 13-104368 | Premium module 1 | Premium client |
2018 | 1 | Client 1 | 13-104368 | Standard module 2 | Premium client |
2018 | 1 | Client 2 | 13-104223 | Standard module 1 | Standard client |
2018 | 1 | Client 2 | 13-104223 | Standard module 2 | Standard client |
2018 | 1 | Client 2 | 13-104292 | Extra module | Standard client |
2018 | 1 | Client 2 | 13-104292 | Standard module 2 | Standard client |
2018 | 1 | Client 2 | 13-104368 | Standard module 1 | Standard client |
2018 | 1 | Client 2 | 13-104368 | Standard module 2 | Standard client |
2018 | 1 | Client 1 | 13-104223 | Standard module 1 | Premium client |
2018 | 1 | Client 1 | 13-104223 | Premium module 2 | Premium client |
2018 | 1 | Client 1 | 13-104223 | Premium module 1 | Premium client |
2018 | 1 | Client 1 | 13-104223 | Standard module 2 | Premium client |
2018 | 1 | Client 1 | 13-104292 | Extra module | Premium client |
2018 | 1 | Client 1 | 13-104292 | Standard module 2 | Premium client |
2018 | 1 | Client 1 | 13-104368 | Standard module 1 | Premium client |
2018 | 1 | Client 1 | 13-104368 | Premium module 2 | Premium client |
2018 | 1 | Client 1 | 13-104368 | Premium module 1 | Premium client |
2018 | 1 | Client 1 | 13-104368 | Standard module 2 | Premium client |
2018 | 1 | Client 2 | 13-104223 | Standard module 1 | Premium client |
2018 | 1 | Client 2 | 13-104223 | Standard module 2 | Premium client |
2018 | 1 | Client 2 | 13-104292 | Extra module | Premium client |
2018 | 1 | Client 2 | 13-104292 | Standard module 2 | Premium client |
2018 | 1 | Client 2 | 13-104368 | Standard module 1 | Premium client |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |