Forum Discussion
Text Manipulation using DAX
Hi,
This is my sample data:
| job | description | item | week |
| 1200662 | aa | 1 | 1 |
| 1200662 | bb | 1 | 1 |
| 1200662 | aa | 2 | 1 |
| 1200662 | bb | 2 | 1 |
| 1200662 | aa | 3 | 2 |
| 1200662 | bb | 3 | 2 |
| 1200662 | aa | 4 | 2 |
| 1200662 | bb | 4 | 2 |
| 1200662 | aa | 5 | 3 |
| 1200662 | bb | 5 | 3 |
| 1200662 | aa | 6 | 3 |
| 1200662 | bb | 6 | 3 |
| 1200662 | aa | 7 | 3 |
| 1200662 | bb | 7 | 3 |
I have 3 filters:
JOB (single select), ITEM (multiselect), WEEK (multiselect)
these are the expected output:
First Scenario
Filtered by Job (Single Select)
Output
| job | description | week |
| 1200662/1-2 | aa | 1 |
| 1200662/1-2 | bb | 1 |
| 1200662/2-3 | aa | 2 |
| 1200662/2-3 | bb | 2 |
| 1200662/5-7 | aa | 3 |
| 1200662/5-7 | bb | 3 |
Second Scenario
Filtered by Week (multiselect)
Output: (selected: 2 and 3)
| job | description | week |
| 1200662/2-3 | aa | 2 |
| 1200662/2-3 | bb | 2 |
| 1200662/5-7 | aa | 3 |
| 1200662/5-7 | bb | 3 |
Third Scenario
Filtered by Item (multiselect)
Output: (selected: 1, 2, 3, 5, and 7 )
| job | description | week |
| 1200662/1-2 | aa | 1 |
| 1200662/1-2 | bb | 1 |
| 1200662/3 | aa | 2 |
| 1200662/3 | bb | 2 |
| 1200662/5, 7 | aa | 3 |
| 1200662/5, 7 | bb | 3 |
I managed to get the first scenario and second scenario.
But I am stuck at third scenario.
I should use measure also, right?
What am I missing?
Any help is appreciated.
Thank you
With your current model. It should be able to deal with any combination of contiguous and non-contiguous blocks of items:
MeasureV2 = VAR itemsT_ = CALCULATETABLE ( DISTINCT ( Table1[item] ), ALL ( Table1[description] ) ) VAR rankedT_ = ADDCOLUMNS ( itemsT_, "@index", RANKX ( itemsT_, [item],, ASC ) ) VAR aux_ = CONCATENATEX ( rankedT_, [item], "|", [@index], ASC ) VAR minIndex_ = 1 VAR maxIndex_ = COUNTROWS ( rankedT_ ) VAR res_ = CONCATENATEX ( rankedT_, VAR last_ = PATHITEM ( aux_, [@index] - 1, INTEGER ) VAR isBlockStart_ = last_ < ( [item] - 1 ) || [@index] = minIndex_ VAR next_ = PATHITEM ( aux_, [@index] + 1, INTEGER ) VAR isBlockEnd_ = ( next_ > ( [item] + 1 ) ) || [@index] = maxIndex_ RETURN IF ( isBlockStart_ && NOT isBlockEnd_, [item] & "-", IF ( isBlockEnd_, [item] & "," ) ), , [@index], ASC ) RETURN SELECTEDVALUE ( Table1[job] ) & "/" & IF ( RIGHT ( res_, 1 ) = ",", LEFT ( res_, LEN ( res_ ) - 1 ), res_ )I'm sure it can be coded more elegantly but I haven't had time to polish it yet
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
The measure:
1. Gets the list of items
2. Ranks them to get them sorted in ascending order
3. Builds a string we can use PATHITEM on, since it simplifies the access to the ranked table
4. For each row in the ranked (sorted table), it checks the previous and next row to see if it's the beginning and/or end of a block.
5. Builds the final string, removing the unwanted "," at the end (this can be done earlier too)
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
12 Replies
With your current model. It should be able to deal with any combination of contiguous and non-contiguous blocks of items:
MeasureV2 = VAR itemsT_ = CALCULATETABLE ( DISTINCT ( Table1[item] ), ALL ( Table1[description] ) ) VAR rankedT_ = ADDCOLUMNS ( itemsT_, "@index", RANKX ( itemsT_, [item],, ASC ) ) VAR aux_ = CONCATENATEX ( rankedT_, [item], "|", [@index], ASC ) VAR minIndex_ = 1 VAR maxIndex_ = COUNTROWS ( rankedT_ ) VAR res_ = CONCATENATEX ( rankedT_, VAR last_ = PATHITEM ( aux_, [@index] - 1, INTEGER ) VAR isBlockStart_ = last_ < ( [item] - 1 ) || [@index] = minIndex_ VAR next_ = PATHITEM ( aux_, [@index] + 1, INTEGER ) VAR isBlockEnd_ = ( next_ > ( [item] + 1 ) ) || [@index] = maxIndex_ RETURN IF ( isBlockStart_ && NOT isBlockEnd_, [item] & "-", IF ( isBlockEnd_, [item] & "," ) ), , [@index], ASC ) RETURN SELECTEDVALUE ( Table1[job] ) & "/" & IF ( RIGHT ( res_, 1 ) = ",", LEFT ( res_, LEN ( res_ ) - 1 ), res_ )I'm sure it can be coded more elegantly but I haven't had time to polish it yet
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
show your work. Are you using CONCATENATEX?
Selected JOB / ITEM = VAR SELECTEDJOB = SELECTEDVALUE('Sheet1 (Job/Item)'[Job]) VAR SELECTEDITEM = CALCULATE ( CONCATENATEX ( VALUES ('Sheet1 (Job/Item)'[Item]) , 'Sheet1 (Job/Item)'[Item], ", ", ISFILTERED('Sheet1 (Job/Item)'[Item]), ASC ) ) RETURN IF( ISFILTERED('Sheet1 (Job/Item)'[Item]), SELECTEDJOB & "/" & SELECTEDITEM, IF( FIRSTNONBLANK('Sheet1 (Job/Item)'[MIN Item],0) = FIRSTNONBLANK('Sheet1 (Job/Item)'[MAX Item],0), SELECTEDJOB & "/" &FIRSTNONBLANK('Sheet1 (Job/Item)'[MIN Item],0), IF( FIRSTNONBLANK('Sheet1 (Job/Item)'[MIN Item],0) <> FIRSTNONBLANK('Sheet1 (Job/Item)'[MAX Item],0), SELECTEDJOB & "/" & FIRSTNONBLANK('Sheet1 (Job/Item)'[MIN Item],0) & "-" & FIRSTNONBLANK('Sheet1 (Job/Item)'[MAX Item],0))))Hi lbendlin
Above is how I handled the 2 scenarios.
But when I start to filter the item, they are showing like this.
yes, I am using also CONCATENATEX with ISFILTERED.
Hi mussaenda
Please explain the logic behind the expected result rather than leaving it up to the reader's guess. Otherwise we're all wasting precious time.
Try this. You'll probably have to tweak it a bit but it should get you close:
Measure = VAR items_ = CALCULATETABLE ( DISTINCT ( 'Sheet1 (Job/Item)'[item] ), ALL ( 'Sheet1 (Job/Item)'[description] ) ) RETURN SELECTEDVALUE ( 'Sheet1 (Job/Item)'[job] ) & "/" & CONCATENATEX ( items_, [item], "-", [item], ASC )Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi AlB ,
Sorry, I thought I have explained it on the expected output.
Assuming that the selected values from the item filter are 1, 2, 3, 5, and 7.
1. Week 1 has only 2 items and both were selected, that's why the output is job 1200662/1-2.
2. Week 2 has 2 items also but only one is selected (item3), that's why the output is job 1200662/3.
3. Week 3 has 3 items but NOT all were selected, that's why the output is job 1200662/5, 7.
They are just duplicating because of the description. which is totally okay.
If you accept 1,2 for the first scenario rather than 1-2 then all scenarios can be covered with a single concatenatex statement.
See if this works for the third measure. First, the model:
Next, the measure:
Job/ Item = VAR SelJob = SELECTEDVALUE('Dim Job'[job]) & "/ " VAR _Item = CONCATENATEX(VALUES('DataTable'[item]), 'DataTable'[item], "-") RETURN SelJob & _Itemand the resulting table:
Hi PaulDBrown ,
Thank you so much for the solution!
Just a question,
Is there a way that for week 3, it is 1200662/5, 7 instead of 5-7?
Because item 6 is not selected.
Thank youuuu
The measure:
1. Gets the list of items
2. Ranks them to get them sorted in ascending order
3. Builds a string we can use PATHITEM on, since it simplifies the access to the ranked table
4. For each row in the ranked (sorted table), it checks the previous and next row to see if it's the beginning and/or end of a block.
5. Builds the final string, removing the unwanted "," at the end (this can be done earlier too)
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
