cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OzzieFrog Member
Member

Replace character when not in a word

Hello all,

 

This issue has had me going for a few hours now so am turning to the smarter brains of this forum Smiley Happy

 

I have a column named "discipline" that can contain up to 9 characters along a drawing reference, in the following format:

 

DrawingDiscipline
WG1234A
WG 125A, C
WG 9090A, C, E, J, K, M, P, S, T
WG 087E

 

The letterts A, C etc cannot be changed in the raw database so I have to import them as is but I'd like to convert A to Architectural, C to Civil, E to Electrical, etc... for the purpose of filtering (I'd actually like to keep the Discipline column as it is)

 

The intent being that I then have a slicer with the listing of Architectural, Civil, Electrical and if I select Architectural, it will show in my table all drawings that have A in the Discipline( in the example above that is WG1234, WG 125 and WG 9090)

 

I have obviously tried to replace values sequentially in power query mode but I end-up with gibberish (A becomes ARCHITECTURAL, but then if E becomes ELECTRICAL I end-up with ARCHITELECTRICALCTURAL...). I have thought of unpivoting the data but I already have a couple million rows and it wouldn't be sustainable.


Any clever solution you can suggest?

 

Thanks in advance.

OF

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Replace character when not in a word

@OzzieFrog

 

Or in fact a leaner version:

 

MeasureNumDisplayedDrawings2_V2 =
COUNTROWS (
    FILTER (
        'All Documents';
        SUMX (
            FilterTableDiscipline;
            FIND ( FilterTableDiscipline[Discipline]; 'All Documents'[Discipline]; 1; 0 )
        )
            > 0
    )
)

Code formatted with   www.daxformatter.com

24 REPLIES 24
Highlighted
Super User
Super User

Re: Replace character when not in a word

Hi @OzzieFrog

 

Here's a general idea  I came up with

1. Create an unconnected table, FilterTable, with two columns:

     a)  FilterTable[Discipline] with your 9 values A, C, E,...  in rows

     b)  FilterTable[LongDiscipline] with the full names of the disciplines, Architectural, Electrical, etc.

 

2. Place a slicer on your report with FilterTable[LongDiscipline] in it. With this you can select the disciplines of interest with their long name rather than just the letter.  

 

3. Place a matrix visual on your report with: 

     a) Table1[Drawing] in rows

     b) This measure in values:

    

Measure =
VAR CurrentDiscipline =
    SELECTEDVALUE ( Table1[Discipline] )
VAR CountOfDisciplines =
    SUMX (
        FilterTable;
        MIN ( FIND ( FilterTable[Discipline]; CurrentDiscipline; 1; BLANK () ); 1 )
    )
RETURN
    IF ( CountOfDisciplines <> 0; CurrentDiscipline )

The measure counts the number of disciplines  present for each drawing and will return

    - a blank if that number is zero

    - the list of disciplines for the drawing if the number of disciplines is >0

 

The matrix will not display drawings with blank in the measure and thus only the drawings of interest will be displayed.

 

Would that help?

     

OzzieFrog Member
Member

Re: Replace character when not in a word

Hello @AlB

 

I'm trying to remote into my work computer to try this. Will it work in a normal table and not a matrix (as this is what I currently have)?

 

Thanks,

OF

Super User
Super User

Re: Replace character when not in a word

@OzzieFrog

 

Yeah, it should

Super User
Super User

Re: Replace character when not in a word

@OzzieFrog

Did it work?

OzzieFrog Member
Member

Re: Replace character when not in a word

@AlB, it worked a treat, very impressive Smiley Happy

 

Only final question I have is that I currently have a card visual display the number of documents filtered on the page. The measure used in this card is very simple:

 

Count of Docs = count('All Documents'[Title])&" documents available"
 
Any idea what I need to do so the number changes with the selection done in the slicer?
Community Support Team
Community Support Team

Re: Replace character when not in a word

Hi @OzzieFrog,


 Only final question I have is that I currently have a card visual display the number of documents filtered on the page. The measure used in this card is very simple:

 

Count of Docs = count('All Documents'[Title])&" documents available"
 
Any idea what I need to do so the number changes with the selection done in the slicer?

 

If I understand your scenario correctly that you create a slicer with All Documents'[Title] column, and you want to calculate the number of the document which has been selected in the slicer?

 

If it is, you could create a measure used in card visual like below.

 

count =
IF (
    ISFILTERED ( 'All Documents'[Title] ),
    COUNT('All Documents'[Title]),
    BLANK ()
)

 

Here is my test sampl with Month.

 

sample.PNG

 

If you still need help, please share some data sample or screenshots to reproduce your scenario so that we could help further on it.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
OzzieFrog Member
Member

Re: Replace character when not in a word

@v-piga-msft, unfortunately this does not work, I assume because I can filter on many things in the report as per screenshot below.

 

The count works well on all my filters, except the one suggested by @AlB

 

Filter screenshot.png

 

Any thoughs?

 

 

Super User
Super User

Re: Replace character when not in a word

Hi @OzzieFrog

 

You just want to show the number of drawings that will be displayed with the measure I suggested earlier?

OzzieFrog Member
Member

Re: Replace character when not in a word

Hi @AlB, yes but as per the screenshot, I may filter with other columns than discipline, so I was hoping for a measure that would work with any of the filters.