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.
Hello,
I thought I would post my own post about this so I can explain the problem well enough. I have tried to look around to find a solution but no luck so far.
First I explain what I discovered as a problem and where I need help.
I made a measure with following
Billed = IF(CONTAINS('Bills'; 'Bills'[OrderNum]; 'Orders'[ID]); TRUE(); FALSE())
The idea is, if Bills don't have in any row my order's order number I know there haven't been genereated a bill for that order.
This works fine and all, but then I realized the data is a bit... "**bleep**y" as in for the field 'Bills'[OrderNum] most of the rows have just 1 value in it (Format: Text) "0001234" for example BUT the field may have MULTIPLE OrderNum's in them like "0001234, 0001235, 0001236".
I supposed CONTAINS() might be able to work as a regular expression but that is not the case, I suppose it tries to look at the whole thing so now in Order[ID] I have "0001234" and if this ID is in one of these fields with multiple ID's in it I get FALSE for the ID to exist.
Sorry if explanation is a bit messy but I tried to make it clear where the problem is. I have tried doing with FIND(LOOKUPVALUE()) kind of things, but just this one is picking my brain now a bit too much.
Thanks a lot in advance DAX gods 🙂
Solved! Go to Solution.
HI @Tumeski,
If you want do some contains related check, I'd like to suggest you use IN keyword with ALLSELECTED function.
Sample:
Billed = IF ( SELECTEDVALUE ( 'Orders'[ID] ) IN ALLSELECTED ( 'Bills'[OrderNum] ); TRUE (); FALSE () )
BTW, current dax functions not support regular expression.
Regards,
Xiaoxin Sheng
HI @Tumeski,
If you want do some contains related check, I'd like to suggest you use IN keyword with ALLSELECTED function.
Sample:
Billed = IF ( SELECTEDVALUE ( 'Orders'[ID] ) IN ALLSELECTED ( 'Bills'[OrderNum] ); TRUE (); FALSE () )
BTW, current dax functions not support regular expression.
Regards,
Xiaoxin Sheng
Hey @v-shex-msft,
Thank you for your reply. This DAX seems to do the trick. In C# Contains() understands to find it inside the string, but yeah I quess only IN is available to do this in DAX world.
HI @Tumeski,
>>This DAX seems to do the trick. In C# Contains() understands to find it inside the string, but yeah I quess only IN is available to do this in DAX world.
For search text itself, I'd like to suggest you use search and find function.
DAX Fridays! #30: SEARCH vs FIND
Regards,
Xiaoxin Sheng
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 | |
94 | |
83 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |