Hi,
I’m creating a measure, using the below virtual table. I know there are blank values in my ‘Work Orders’[Primary Code] column and I’d like to filter them out, I can’t seem to work out how to filter them out.
Worst#1 ISD’s by Primary Code =
VAR _vTable =
ADDCOLUMNS (
SUMMARIZE ( ‘Work Orders’, ‘Work Orders’[Primary Code] ),
“@Primary Code”, ‘# Measures - ISD’‘s’[ISD’s] )
Thanks
David
It’s probably very simple but I’ve not worked it out.
Can you please post your PBIX or a representative version with some dummy data in it?
Also, big thumbs up for using the @ convention to indicate virtual table columns. However, in terms of notation, if this is a measure
then best practice is not to show the table reference for measures but just to denote them in this way:
“@Primary Code”, [ISD’s]
In my haste I neglected to remove the table reference from my measure, I usually do honest 
Will get a representative PBIX uploaded shortly.
Worst#1 ISD's by Primary Code =
VAR _vTable =
ADDCOLUMNS (
FILTER (
VALUES ( 'Work Orders'[Primary Code] ),
'Work Orders'[Primary Code] <> ""
),
"@Primary Code", [ISD's]
)
RETURN
_vTableEDNA PBIX.pbix (26.7 KB)
I wanted to use the logic that @MudassirAli used in his entry for Supplier Insight (If I remember correctly), so due to process errors the Primary Code field isn’t being filled out with every entry but we want to track those that are filled.
So I wanted to create Top Recurring values but it is returning blanks due to the high number of them. In the test data PBIX I sent on I’d want the blanks to be ignored so Primary Code A would be the top returning value.
Okay, I think I got both measures working according to spec:
You were extremely close on the first one – all you needed was to wrap your vTable variable in CALCULATETABLE and add a filter condition to get rid of the blanks:
Worst#1 ISD’s by Primary Code = VAR _vTable =
CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE( 'Work Orders', 'Work Orders'[Primary Code] ),
"@Primary Code", [ISD's]
),
FILTER( 'Work Orders', 'Work Orders'[Primary Code] <> BLANK() )
)
VAR _TopN =
TOPN( 1, _vTable, [@Primary Code], DESC )
VAR Result =
MAXX( _TopN, [@Primary Code] )
RETURN
Result
The second one took a little more work – I used CONCATENATEX in the event that there are multiple codes with the same worst ISD value. I also used UNICHAR(10) as the delimiter to put each code on a different line in the card, but if you wanted to string them together you could substitute ". " as the delimiter parameter value.
Worst ISD by Primary Code Name =
VAR _vTable =
CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE( 'Work Orders', 'Work Orders'[Primary Code] ),
"@Primary Code", [ISD's]
),
FILTER( 'Work Orders', 'Work Orders'[Primary Code] <> BLANK() )
)
VAR NumWorst = [Worst#1 ISD's by Primary Code]
VAR Result =
CONCATENATEX(
FILTER( _vTable, [@Primary Code] = NumWorst ),
'Work Orders'[Primary Code],
UNICHAR( 10 ),
'Work Orders'[Primary Code], ASC
)
RETURN
Result
I hope this is helpful. Full solution file posted below.
@BrianJ Thanks for this, really appreciate the quick turnaround and you helping me understand that last final step. I hadn’t thought about the ties possibility so thank you for the foresight on that also.
Truly my pleasure – these types of virtual table DAX problems are some of my favorites to work out. I’m also working on a video on debugging techniques for virtual tables, and this will be a terrific example to illustrate a number of those points.
If you haven’t seen it already, Patrick LeBlanc has a terrific video out recently about how to work through these sorts of problems in DAX Studio or Tabular Editor. Highly recommended:
These are working great and I added in a last 7 days by using DATESBETWEEN as per below. for the first measure that counts the number
Worst#1 ISD’s by Primary Code Last 7 Days =
VAR _vTable =
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE ( ‘Work Orders’, ‘Work Orders’[Primary Code] ),
“@Primary Code”, [ISD’s]
),
FILTER ( ‘Work Orders’, ‘Work Orders’[Primary Code] <> BLANK () ),
DATESBETWEEN (Date_Table[Date],
MAX ( Date_Table[Date] ) - 7,
MAX ( Date_Table[Date] ))
)
VAR _TopN =
TOPN ( 1, _vTable, [@Primary Code], DESC )
VAR Result =
MAXX ( _TopN, [@Primary Code] )
RETURN
Result
I’ve not been able to get it working for the below formula as it just returns a blank
CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE( ‘Work Orders’, ‘Work Orders’[Primary Code] ),
“@Primary Code”, [ISD’s]
),
FILTER( ‘Work Orders’, ‘Work Orders’[Primary Code] <> BLANK() )
)
VAR _TopN =
TOPN( 1, _vTable, [@Primary Code], DESC )
VAR Result =
MAXX( _TopN, [@Primary Code] )
RETURN
Result
Any help, gratefully received.
David
I’ve got to run to a meeting and don’t have time to test this theory, but I think what’s happening is related to how you’re calculating last seven days. I think what you DAX is doing is taking the last seven days of your date table, which would explain why it’s returning blanks. You can run a DAX query in DAX Studio or Tabular Editor to test that. Instead, try using a filter condition <= TODAY() && >= TODAY() -7.
Let me know how that goes, and if need be I can dig in in detail later.
Hope that’s helpful.
Brian
Great, thanks Brian. Will let you know how it goes.
The measure Worst#1 ISD’s by Primary Code seems to be doing it’s job dynamically when I use the date filter.
The measure Worst ISD by Primary Code Name is still returning a blank with what you suggested. I was hoping to make my measures dynamic to the date selected as I want to have measures that show
Last 7 days
Last 14 days
Last 28 days
Each having the Worst#1 ISD’s by Primary Code and the Worst ISD by Primary Code Name.
OK, got it. We can definitely make that dynamic. If you can post your current PBIX, I can work through it this evening.
Thanks!
If we look at the error message on the Primary Codename measure, we’ll see that it’s returning a table when it expects a scalar:
So if we run a DAX query on _Filter the problem becomes apparent – we hadn’t accounted for the scenario where two primary codes would tie for worst:
So what we need here is a function that will take a table of results and return a scalar – CONCATENATEX to the rescue:
and Bingo!
Your hardwired previous seven-day measure actually works fine because your day table dynamically ends at the last date in your fact table. However, to make this dynamic in the way you want, what I would do instead is add a Period Table slicer and customize it for the period you want (e.g., 7 days, 14 days, 24 days, etc.)
@JarrettM does this on almost all of this challenge entries and has an excellent video on how to implement this technique:
I hope this is helpful. Full solution file attached. Cool problem – enjoyed working with you on this.
– Brian
eDNA Forum - ISD Virtual Table Solution 1.1.pbix (54.5 KB)
Hey Brian,
I’m just about to turn the light out after reading some more of The Signal and the Noise…thought I’d check the forum and boom, there you go! Genuine big thanks on this and glad you enjoyed helping me on this. The more I ask questions on here the more my mind is being opened. Hopefully it’ll sink in haha!
Will check this out properly in the morning.
Sure – just give a shout if you have any questions after working through the full solution.
Excellent book choice BTW…
– Brian
Thanks for your help on this already Brian, I just have one last tweak to make and I can’t seem to resolve it.
I no longer need to create a Last 7 Days measure etc, just need the measures to work dynamically which they pretty much are with one slight blip.
I need to display the Top 3 Primary Codes by their Name and by their Number. So one card for the Primary Codes themselves and the Card below for the count/number. This works fine except when there is a tie. In the image I’ve posted the Worst 1 and Worst 2 cards both show the same values when I’d hoped the second card would drop down to the second values/numbers as per the graph below. So the #1Worst Cards would show A, G & 9, the #2Worst Cards would show B, BT, BZ, E & 4 and the #3Worst Card would show H and 2. Thanks in advance for any assistance given.
