Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Drawing | Discipline |
WG1234 | A |
WG 125 | A, C |
WG 9090 | A, C, E, J, K, M, P, S, T |
WG 087 | E |
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
Solved! Go to 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 ) )
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?
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?
@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:
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.
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
@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
Any thoughs?
Hi @Anonymous
You just want to show the number of drawings that will be displayed with the measure I suggested earlier?
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.
@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
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.
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 )
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.
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
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
Ok I think I got it: http://s000.tinyupload.com/index.php?file_id=02620565943583883395
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |