Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I want to search a text (COWN /CUST Owned /Customer owned) in a description as shown below:
And here is my criteria
1- If Description has a text COWN/CUST owned or Customer owned and Type is not ZNB1 then return me value of 1 else 2.
Here is what i want.
So far i tried this dax measure but this is not working
Any help is greatly appreacited !
Thanks,
Tejaswi
Solved! Go to Solution.
Hey,
I just realized that the measure in my previous post just solves the text search part of your question, but is neglecting the second condition regarding the value of the product type.
This measure considers all of the requirements:
Measure 2 = var searchtable = DATATABLE("searchtext", STRING , {{"CUSTOMER OWNED"}, {"COWN"}, {"CUST OWN"}}) return //COUNTROWS(searchtable) IF(HASONEVALUE('Sheet1'[Material description ]) , SUMX( //VALUES(Sheet1[Material description ]) 'Sheet1' , var _d = ROW("d" , 'Sheet1'[Material description ]) return IF( 'Sheet1'[Material type] = "ZNB1" , 2 , var isFound = MAXX( ADDCOLUMNS( GENERATEALL( _d , searchtable ) , "contains" , IF(FIND([searchtext] , [d] ,1 , BLANK()) = 1 , 1, BLANK()) ) , [contains] ) return IF(NOT(ISBLANK(isFound)), 1 , 2) ) ) , BLANK() )
I added a check (using HASONEVALUE) to avoid summing the values returned by the conditions as I wasn't able to interpret the total line.
And a fraction of the table visual:
Hopefully this is what you are looking for.
Regards,
Tom
IF(FIND([searchtext] , [d] ,1 , BLANK()) >= 1 , 1, BLANK())
Hey @Anonymous
it seemed that I overlooked a tiny thing between testing and copying the DAX to the post.
I highlighted the change in the DAX statement below:
Measure 2 = var searchtable = DATATABLE("searchtext", STRING , {{"CUSTOMER OWNED"}, {"COWN"}, {"CUST OWN"}}) return //COUNTROWS(searchtable) IF(HASONEVALUE('Sheet1'[Material description ]) , SUMX( //VALUES(Sheet1[Material description ]) 'Sheet1' , var _d = ROW("d" , 'Sheet1'[Material description ]) return IF( 'Sheet1'[Material type] = "ZNB1" , 2 , var isFound = MAXX( ADDCOLUMNS( GENERATEALL( _d , searchtable ) , "contains" , IF(FIND([searchtext] , [d] ,1 , BLANK()) >= 1 , 1, BLANK()) ) , [contains] ) return IF(NOT(ISBLANK(isFound)), 1 , 2) ) ) , BLANK() )
As FIND(...) returns the position of the searchtext inside the text I changed
IF(FIND([searchtext] , [d] ,1 , BLANK()) = 1 , 1, BLANK())
to this
IF(FIND([searchtext] , [d] ,1 , BLANK()) >= 1 , 1, BLANK())
Regards,
Tom
Hey,
please provide pbix and/or xlsx that contains sample data but still represents your data model (the tables necessary or to be considered to solve your problem), upload the file(s) to onedrive or dropbox and share the link.
Regards,
Tom
Hi @TomMartens
Thanks for your reply!
Please find dropbox link to the sample data file.
https://www.dropbox.com/s/st05yn0wq61kr98/sample%20Data.pbix?dl=0
I am looking for a dax measure. It looks like the measure I have is completely wrong.
In this sample data :
Since the following products don't fit the criteria and therefore the material description should be highlighted.
- 1433664
-1011046
-1372704
-1233491
Your help in resolving this is greatly appreaciated!
Thanks,
Tejaswi
Hey @Anonymous ,
this measure
Measure 2 = var searchtable = DATATABLE("searchtext", STRING , {{"CUSTOMER OWNED"}, {"COWN"}, {"CUST OWN"}}) return //COUNTROWS(searchtable) SUMX( VALUES(Sheet1[Material description ]) , var _d = ROW("d" , 'Sheet1'[Material description ]) return MAXX( ADDCOLUMNS( GENERATEALL( _d , searchtable ) , "contains" , IF(FIND([searchtext] , [d] ,1 , BLANK()) = 1 , 1, BLANK()) ) , [contains] ) )
allows to create this table visual:
Hopefully this is what you are looking for.
Regards,
Tom
Hi @TomMartens ,
Thanks for providing me the solutions.
However, is it possible to show all the Product number and not just 2 which we get with this solution?
Really appreaciate it!
Thanks,
Tejaswi
Hey,
I just realized that the measure in my previous post just solves the text search part of your question, but is neglecting the second condition regarding the value of the product type.
This measure considers all of the requirements:
Measure 2 = var searchtable = DATATABLE("searchtext", STRING , {{"CUSTOMER OWNED"}, {"COWN"}, {"CUST OWN"}}) return //COUNTROWS(searchtable) IF(HASONEVALUE('Sheet1'[Material description ]) , SUMX( //VALUES(Sheet1[Material description ]) 'Sheet1' , var _d = ROW("d" , 'Sheet1'[Material description ]) return IF( 'Sheet1'[Material type] = "ZNB1" , 2 , var isFound = MAXX( ADDCOLUMNS( GENERATEALL( _d , searchtable ) , "contains" , IF(FIND([searchtext] , [d] ,1 , BLANK()) = 1 , 1, BLANK()) ) , [contains] ) return IF(NOT(ISBLANK(isFound)), 1 , 2) ) ) , BLANK() )
I added a check (using HASONEVALUE) to avoid summing the values returned by the conditions as I wasn't able to interpret the total line.
And a fraction of the table visual:
Hopefully this is what you are looking for.
Regards,
Tom
Hi @TomMartens ,
Thanks a Lot. This code works as expected!
Appreacite all your help!
Thanks,
Tejaswi
Hi @TomMartens,
I just noticed, that this code works only if the text is at the beginning.
if text is anywhere in the string or at the end it doesn't work.
Can you please take a look?
Thanks
Tejaswi
IF(FIND([searchtext] , [d] ,1 , BLANK()) >= 1 , 1, BLANK())
Hey @Anonymous
it seemed that I overlooked a tiny thing between testing and copying the DAX to the post.
I highlighted the change in the DAX statement below:
Measure 2 = var searchtable = DATATABLE("searchtext", STRING , {{"CUSTOMER OWNED"}, {"COWN"}, {"CUST OWN"}}) return //COUNTROWS(searchtable) IF(HASONEVALUE('Sheet1'[Material description ]) , SUMX( //VALUES(Sheet1[Material description ]) 'Sheet1' , var _d = ROW("d" , 'Sheet1'[Material description ]) return IF( 'Sheet1'[Material type] = "ZNB1" , 2 , var isFound = MAXX( ADDCOLUMNS( GENERATEALL( _d , searchtable ) , "contains" , IF(FIND([searchtext] , [d] ,1 , BLANK()) >= 1 , 1, BLANK()) ) , [contains] ) return IF(NOT(ISBLANK(isFound)), 1 , 2) ) ) , BLANK() )
As FIND(...) returns the position of the searchtext inside the text I changed
IF(FIND([searchtext] , [d] ,1 , BLANK()) = 1 , 1, BLANK())
to this
IF(FIND([searchtext] , [d] ,1 , BLANK()) >= 1 , 1, BLANK())
Regards,
Tom
User | Count |
---|---|
86 | |
83 | |
67 | |
61 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
84 | |
70 |