Sorry- crashing on a project for @sam.mckay, and wasn’t able to get back to this today. Know how I will solve this - just need to write it up, which I’ll do on Tues.
- Brian
1 Like
Hey @BrianJ, you don’t need to apologise, you’ve been an unbelievable help so far. Will catch up with you later. 
Just wanted to let you know I haven’t at all forgotten about this. Project for Sam just took way longer than expected.
Will absolutely get you a response on Thursday.
Thanks for your patience.
- Brian
1 Like
@BrianJ Not for a second did I think you’d forgotten so don’t worry, I appreciate the message all the same. I figured the day job was keeping you busy and out of mischief 
Sorry – been an absolutely bonkers week, but I finally had a bit of time to sit down and work through this one. Basically just measure branched it into submission using the same general logic that we developed the last go around (I.e., first measure in each round calculates the # of ISDs that’s the “worst” after filtering out the records from the previous round.
So, in this case the first round determines that 9 is the worst. So then the second round we use the same methodology to calculate the worst, but this time the virtual table filters out all records where the code has a score equal to the worst of round one (9 in this case). That the third round does the same thing, but filters out all records with a score equal to the worst in rounds one and two (9 and 4 here).
The measures to find the code name follow the same process to determine the worst ISD score for that round, and then filter the virtual table to find the name(s) that correspond to that score.
The DAX is lengthy (due in part to having to factor in the possibility of ties) but the logic is pretty straightforward when you work your way through it.
Here are the third round measures (3A and 3B):
Worst#3 ISD’s by Primary Code =
VAR Worst1 = [Worst#1 ISD's by Primary Code]
VAR Worst2 = [Worst#2 ISD's by Primary Code]
VAR Filt1 =
FILTER( 'Work Orders', 'Work Orders'[Primary Code] <> BLANK() )
VAR _vTable =
ADDCOLUMNS(
SUMMARIZE( Filt1, 'Work Orders'[Primary Code] ),
"@ISDs", [ISD's]
)
VAR _TopN =
TOPN(
1,
FILTER( _vTable, [@ISDs] <> Worst1 && [@ISDs] <> Worst2 ),
[@ISDs], DESC
)
VAR Worst3 =
MINX( _TopN, [@ISDs] )
RETURN
Worst3
Worst#3 ISD's by Primary Code Name =
VAR Worst1 = [Worst#1 ISD's by Primary Code]
VAR Worst2 = [Worst#2 ISD's by Primary Code]
VAR Filt1 =
FILTER( 'Work Orders', 'Work Orders'[Primary Code] <> BLANK() )
VAR _vTable =
ADDCOLUMNS(
SUMMARIZE( Filt1, 'Work Orders'[Primary Code] ),
"@ISDs", [ISD's]
)
VAR _TopN =
TOPN(
1,
FILTER( _vTable, [@ISDs] <> Worst1 && [@ISDs] <> Worst2 ),
[@ISDs], DESC
)
VAR Worst3 =
MINX( _TopN, [@ISDs] )
VAR _Filter =
FILTER( _vTable, [@ISDs] = Worst3 )
VAR Result =
CONCATENATEX(
_Filter,
'Work Orders'[Primary Code],
", ",
'Work Orders'[Primary Code], ASC
)
RETURN
Result
I hope this is helpful. Full solution file attached below.
Super fun problem.
– Brian
eDNA Forum - ISD Virtual Table Solution 1.4.pbix (56.3 KB)
2 Likes
Fantastic Brian, it’s been great to learn through this. I’m on holiday for a week so not sure if I’ll log on and work this through just yet, but look forward to putting it into place.
Good to hear you found it a fun problem to work through!
Have a great weekend
David
Hope you have a wonderful vacation. Whenever you do get back to this, check out the following thread where I wrote up the methodology I used to crack this one as a general tip. You may want to step through it in the same way, so you can see what the measure is doing within each variable. Makes it much easier to see the logic when you break it down into its component parts.
– Brian
1 Like
Lovely, thank you. I will certainly work through this.
David