Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 🙂

 

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

@Anonymous

 

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

View solution in original post

24 REPLIES 24
AlB
Super User
Super User

Hi @Anonymous

 

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?

     

Anonymous
Not applicable

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

@Anonymous

 

Yeah, it should

@Anonymous

Did it work?

Anonymous
Not applicable

@AlB, it worked a treat, very impressive 🙂

 

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?

Hi @Anonymous,


 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.
Anonymous
Not applicable

@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?

 

 

Hi @Anonymous

 

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

Anonymous
Not applicable

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.

Hey @Anonymous

 

I have to go right now but try a  measure in which you recreate what we did before to show the drawings.  For instance with SUMMARIZECOLUMNS with the drawings in the first column and then using our previous measure in the second column. Then count the nonblanks on that second column.

 

Cheers 

@Anonymous

 

Something like:

 

MeasureNumDisplayedDrawings =    
COUNTROWS (
    SUMMARIZECOLUMNS (
        Table1[Drawing];
        "FilteredDisciplines"; [Measure]
    )
)

where [Measure] is the measure we created earlier.

Anonymous
Not applicable

@AlB, first of happy new year 🙂

 

I've tried to recreate your measure but I'm not able to add the measure we created, mainly due to the fact that summaricolumns will only let me enter tables after the first argument. Also, in the example you gave, is "FilteredDiscplines" the name of the table we created for the slicer?

Thanks a lot @Anonymous. An awesome new year to you too Smiley Happy

 

I'm not sure I understand what the issue is.  "FilteredDisciplines" is just the name I've given to the column we are creating within SUMMARIZECOLUMNS. It's nothing that existed before.

What we do in [MeasureNumDisplayedDrawings] is create a dynamic table in which we have as first column the drawings

and a second column, "FilteredDisciplines", where we use the previously created [Measure].  In the "FilteredDisciplines" column, we will thus have a 1 in the rows where the drawing comes up in the selected filter context and a blank where it doesn't. SUMMARIZECOLUMNS eliminates the rows with a blank, so by using COUNTROWS on the dynamically created table, we'll have the number of drawings displayed.


 

 

 

Anonymous
Not applicable

Hello @AlB,

 

Sorry for the delayed response, I've been travelling for work and just came back to this. Everything I wanted to do with my model, except the issue highlighted above.

 

WHen trying to implement the solution you provided and when I use the measure in a card, I get the following error: "MdxScript(Model) (21,2) Calculation error in measure 'All Documents'[MeasureNumDisplayedDrawings]: SummarizeColumns() and AddMissingItems() may not be used in this context.

 

To summarise, here is what I have:

  . numerous filters to help me reduce the number of lines displayed in a table

  . one of these filters uses the column Long Discipline you helped me create which links back to this measure:

 

 
Discipline (Measure) = 
VAR CurrentDiscipline =
    SELECTEDVALUE ('All Documents'[Discipline])
VAR CountOfDisciplines =
    SUMX (
        FilterTableDiscipline,
        MIN ( FIND (FilterTableDiscipline[Discipline] , CurrentDiscipline, 1, BLANK () ), 1 )
    )
RETURN
    IF ( CountOfDisciplines <> 0, CurrentDiscipline )
 
 
 . one card on the page that counts the number of records in "Title".

 

This card shows the current number of records being filtered, apart from when I use the discipline filter.

 

I have tried using the formula you suggested but get the error I mentioned at the beginning of the post:

  

MeasureNumDisplayedDrawings = 
COUNTROWS (
 SUMMARIZECOLUMNS(
'All Documents',"FilteredDisciplines",[Discipline (Measure)])
)

 

Any thoughts on what I am doing wrong?

 

Thanks in advance.

OF

@Anonymous

 

Well I don't remember this well but one thing you seem to be doing wrong is copy/paste. The [MeasureNumDisplayedDrawings] I posted has a column as first argument of the SUMMARIZECOLUMNS (which is what the function expects). Yours has a table.

 

 

Anonymous
Not applicable

Thanks @AlB. I have modified the formula to match your suggestions but unfortunately come up with the same error as the one I quoted.

 

I'll keep browsing the net for other ways to count the rows that are filtered by this type of measure and if I don't find a solution, I'll have to remove the count visual altogether.

 

OF

@Anonymous

If you share the pbix indicating where the issue is I'll try to take a look. All i remember now is that it was working on my end.

Cheers

Anonymous
Not applicable

Hi @AlB,

 

I've managed to sanitize the data but can't find a way to upload the file onto here.

 

We do use a corporate version of OneDrive but I don't have the ability to share a file with someone outside of our network, do you have a suggestion?

 

Thanks in advance.

OF

@Anonymous

You can upload the file to a site like tinyupload.com (no sign-in required) and post the URL here

Anonymous
Not applicable

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.