cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mussaenda
Super User I
Super User I

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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




2 ACCEPTED SOLUTIONS
AlB
Super User III
Super User III

@mussaenda 

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 

 

SU18_powerbi_badge

 

 

View solution in original post

AlB
Super User III
Super User III

@mussaenda 

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 

 

SU18_powerbi_badge

 

View solution in original post

12 REPLIES 12
AlB
Super User III
Super User III

@mussaenda 

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 

 

SU18_powerbi_badge

 

View solution in original post

AlB
Super User III
Super User III

@mussaenda 

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 

 

SU18_powerbi_badge

 

 

View solution in original post

@AlB 

 

You're superb!

I want to understand the logic you did.

 

Wow. Just wow.

How can I thank you enough?

 

 

Thank you again!

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




AlB
Super User III
Super User III


@mussaenda wrote:

How can I thank you enough?

 


@mussaenda 

Perhaps kudoing/marking the answer as solution 😉?

 

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 

SU18_powerbi_badge

PaulDBrown
Super User II
Super User II

@mussaenda 

See if this works for the third measure. First, the model:

model.PNG

 

Next, the measure:

Job/ Item = 
VAR SelJob = SELECTEDVALUE('Dim Job'[job]) & "/ "
VAR _Item = CONCATENATEX(VALUES('DataTable'[item]), 'DataTable'[item], "-")
RETURN
SelJob & _Item

and the resulting table:

result.PNG





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




AlB
Super User III
Super User III

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 

 

SU18_powerbi_badge

 

Hi @AlB ,

 

Sorry, I thought I have explained it on the expected output.

Untitled1.png

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.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




If you accept 1,2 for the first scenario rather than 1-2 then all scenarios can be covered with a single concatenatex statement.

I wanted to.

But the issue here is,

sometimes for one week, the are 25 items.

 

It is not acceptable to show 1, 2, 3, 4, 5, ... 25

 

That's why they required 1-25.

 

Is there other way to do it?

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




lbendlin
Super User III
Super User III

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.

Untitled.png

yes, I am using also CONCATENATEX with ISFILTERED.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.