Weird table creation for DAX gurus

[KHorseman]

I am really beating my head against a wall on this one. Here's the scenario:

In this table I have 2 fields. Call them "Interviewer" and "Contractor". Interviewer is a field with user's full names. Contractor...was designed by an idiot. It's a picklist of first names of users, plus one extra name that isn't a user*. Contractor is sometimes blank when Interviewer is not. Interviewer is never blank. When it is not blank, Contractor usually matches the first name of Interviewer, but not always. Sometimes it's a different person's name and sometimes it's that one extra name. It looks something like this:

Interviewer	Contractor
Stephen Fry	Stephen
Tim McInnerny	Rik
Rowan Atkinson	Rowan
Stephen Fry	Stephen
Hugh Laurie	Hugh
Rowan Atkinson	Rowan
Tony Robinson	Tony
Tim McInnerny	Rowan
Tony Robinson	Tony
Tim McInnerny	Tim
Hugh Laurie
Stephen Fry	Stephen
Hugh Laurie	Hugh
Stephen Fry
Miranda Richardson	Miranda
Rowan Atkinson	Rowan
Rowan Atkinson	Rik
Stephen Fry	Stephen
Tony Robinson	Rowan
Tony Robinson	Rik
Rowan Atkinson
Tim McInnerny	Tim
Rowan Atkinson	Rowan
Miranda Richardson	Miranda
Miranda Richardson	Miranda
Tony Robinson	Tony
Tim McInnerny	Rik
Tony Robinson	Tony
Rowan Atkinson	Rik
Rowan Atkinson
Tim McInnerny	Tim
Miranda Richardson	Miranda
Rowan Atkinson	Rowan
Rowan Atkinson	Rowan
Miranda Richardson	Miranda
Hugh Laurie	Hugh
Rowan Atkinson	Rowan
Tim McInnerny	Tim
Rowan Atkinson	Rowan
Tony Robinson
Tony Robinson	Tony
Tony Robinson	Tony
Tim McInnerny	Tim
Tim McInnerny	Rik
Rowan Atkinson	Rowan
Tim McInnerny
Rowan Atkinson	Rowan

I need to create a second table that gives me all the contractor full names in one column with the matching interviewer first name, plus that one extra name. So the end product would look like:

Interviewer	Contractor
Rowan Atkinson	Rowan
Stephen Fry	Stephen
Hugh Laurie	Hugh
Tony Robinson	Tony
Tim McInnerny	Tim
Miranda Richardson	Miranda
	Rik

I can't do this in the query editor for reasons I won't get into. I can't get there with anything as simple as SUMMARIZE because of the missmatch rows and the fact that there is never a row with a blank Contractor and "Rik" under Interviewer. I am at a loss.

@OwenAuger @Greg_Deckler @MattAllington@Seth_C_Bauer @Anonymous save me!

*This is a little white lie. There are actually multiple names like this, but to keep my example simple I'm claiming there's just 1. Also the roster of names in both columns is constantly changing so it's not as if I can hard-code some name values into a filter formula.

[Vvelarde · Accepted solution]

@KHorseman

I think i got it.

In your main table add 2 columns:

Contractor equal to Interviewer =
IF (
    LEFT ( Table1[Interviewer], FIND ( " "; Table1[Interviewer] ) - 1 )
        = Table1[Contractor],
    1,
    0
)

ExistContractorinallIntervierwersColumn =
COUNTROWS (
    FILTER (
        Table1,
        Table1[Contractor equal to Interviewer] <> 0
            && Table1[Contractor] = EARLIER ( Table1[Contractor] )
    )
)

And a New Table:

TheTable =
UNION (
    SUMMARIZE (
        FILTER ( Table1, Table1[Contractor equal to Interviewer] = 1 ),
        Table1[Contractor],
        Table1[Interviewer]
    ),
    SUMMARIZE (
        FILTER (
            Table1,
            Table1[Contractor equal to Interviewer] = 0
                && Table1[Contractor] <> BLANK ()
                && Table1[ExistContractorinallIntervierwersColumn] = BLANK ()
        ),
        Table1[Contractor],
        "Interviewer", BLANK ()
    )
)

* I just a Fred to testing.

[KHorseman · Accepted solution]

OK I think I've got this figured out now. @Vvelarde I pretty much just compressed your solution into a single godawful table formula. It ain't pretty, but it does the job.

UNION(
	SUMMARIZE(
		FILTER(
			ADDCOLUMNS(
				VALUES(TableName[Contractor]),
				"IntvCheck",
				VAR conname = FIRSTNONBLANK(TableName[Contractor], 1)
				RETURN CALCULATE(
					COUNTROWS(
						FILTER(
							ALL(TableName),
							NOT(ISBLANK(TableName[Interviewer])) &&
							LEFT(
								TableName[Interviewer],
								FIND(" ", TableName[Interviewer]) - 1
							) = conname
						)
					)
				),
				"Interviewer",
				BLANK()
			),
			[IntvCheck] = 0 &&
			NOT(ISBLANK(TableName[Contractor]))
		),
		[Interviewer], [Contractor]
	),
	ADDCOLUMNS(
		SUMMARIZE(
			FILTER(
				TableName,
				NOT(ISBLANK(TableName[Interviewer]))
			),
			TableName[Interviewer]
		),
		"Contractor",
		LEFT(
			[Interviewer],
			FIND(" ", [Interviewer]) - 1
		)
	)
)

That might be the most duct-tape DAX formula I've ever seen.

[Vvelarde · Accepted solution]

@KHorseman

I think i got it.

In your main table add 2 columns:

Contractor equal to Interviewer =
IF (
    LEFT ( Table1[Interviewer], FIND ( " "; Table1[Interviewer] ) - 1 )
        = Table1[Contractor],
    1,
    0
)

ExistContractorinallIntervierwersColumn =
COUNTROWS (
    FILTER (
        Table1,
        Table1[Contractor equal to Interviewer] <> 0
            && Table1[Contractor] = EARLIER ( Table1[Contractor] )
    )
)

And a New Table:

TheTable =
UNION (
    SUMMARIZE (
        FILTER ( Table1, Table1[Contractor equal to Interviewer] = 1 ),
        Table1[Contractor],
        Table1[Interviewer]
    ),
    SUMMARIZE (
        FILTER (
            Table1,
            Table1[Contractor equal to Interviewer] = 0
                && Table1[Contractor] <> BLANK ()
                && Table1[ExistContractorinallIntervierwersColumn] = BLANK ()
        ),
        Table1[Contractor],
        "Interviewer", BLANK ()
    )
)

* I just a Fred to testing.

[Anonymous]

Well, that's a mess 🙂

So, if I am summarizing by Interviewer, how do I know which contractor to grab?  I see Tim Mc is { Tim, Rowan, Rik } -- which was odd.  

I mean, is the ask literally for the distinct set of Interviewer (plus a blank), and their first name?  Can we just ignore the Contractor column?

[KHorseman]

"I mean, is the ask literally for the distinct set of Interviewer (plus a blank), and their first name?  Can we just ignore the Contractor column?"

@AnonymousI wish. The problem is "Rik". In my example here Rik is only in the Contractor column, never in Interviewer. I have these extra non-matching values in the Contractor column and I need those too. That's what's killing me. For all the names in the Contractor column I could certainly just make a column that duplicates their first name, and that would give me what I need. ADDCOLUMNS(SUMMARIZE...), easy. Except that would leave off the extra Contractors that never appear in the Interviewer column, so it only gets me part of the way there.

@Vvelardethat looks just about perfect! I'm going to play with it a little more and see if I can find any way to simplify it. This data model is a mess already so I don't want to add any columns if I can possibly avoid it.

[KHorseman · Accepted solution]

OK I think I've got this figured out now. @Vvelarde I pretty much just compressed your solution into a single godawful table formula. It ain't pretty, but it does the job.

UNION(
	SUMMARIZE(
		FILTER(
			ADDCOLUMNS(
				VALUES(TableName[Contractor]),
				"IntvCheck",
				VAR conname = FIRSTNONBLANK(TableName[Contractor], 1)
				RETURN CALCULATE(
					COUNTROWS(
						FILTER(
							ALL(TableName),
							NOT(ISBLANK(TableName[Interviewer])) &&
							LEFT(
								TableName[Interviewer],
								FIND(" ", TableName[Interviewer]) - 1
							) = conname
						)
					)
				),
				"Interviewer",
				BLANK()
			),
			[IntvCheck] = 0 &&
			NOT(ISBLANK(TableName[Contractor]))
		),
		[Interviewer], [Contractor]
	),
	ADDCOLUMNS(
		SUMMARIZE(
			FILTER(
				TableName,
				NOT(ISBLANK(TableName[Interviewer]))
			),
			TableName[Interviewer]
		),
		"Contractor",
		LEFT(
			[Interviewer],
			FIND(" ", [Interviewer]) - 1
		)
	)
)

That might be the most duct-tape DAX formula I've ever seen.

[OwenAuger · Accepted solution]

Hi @KHorseman

I was just playing with this and saw your solution earlier 🙂

Here is a version I came up with that should follow exactly the same logic, I've just used variables to split up the steps.

TableFinal = 

VAR Interviewers_Fullname_Firstname =
// Two-column table with Interviewers Fullname & Firstname
    ADDCOLUMNS (
        EXCEPT ( VALUES ( TableName[Interviewer] ), { BLANK () } ),
        "Contractor", LEFT ( TableName[Interviewer], FIND ( " ", TableName[Interviewer] ) - 1 )
    )

VAR Interviewers_Firstname =
// Interviewers Firstname only, used below
    SELECTCOLUMNS ( Interviewers_Fullname_Firstname, "Firstname", [Contractor] )
    
VAR Contractors =
// Contractors from second column of original table
EXCEPT ( VALUES ( TableName[Contractor] ), { BLANK () } )

VAR ContractorsWhoArentInterviewers =
// Two-column table containing blanks & Contractors who aren't interviewers
    GENERATE (
        { BLANK () },
        EXCEPT (
            Contractors,
            Interviewers_Firstname
        )
    )

RETURN
    UNION ( Interviewers_Fullname_Firstname, ContractorsWhoArentInterviewers )

[KHorseman]

@OwenAugerI love it! And there are a couple of functions there that make a lot more sense to me now that I see how you're using them. Thank you.

[Anonymous]

What is this madness?

{ BLANK () }

[Sean]

As always @OwenAuger's solution very clean and elegant

@Anonymousthat's the new table constructor

However I'll defer to Owen as to its use in the above since I'm a bit perplexed about this too...

specifically - EXCEPT ( VALUES ( TableName[Contractor] ), { BLANK ( ) } )

seems to return the same as - VALUES ( TableName[Contractor] ) - while I expeceted it to remove the blanks

plus this solution returns an extra row of both columns blank vs @Vvelarde's solution

[OwenAuger]

That's right, the intent of EXCEPT ( ..., { BLANK ( ) } ) was simply to remove blanks.

When testing, all my empty text values were blanks, but we should handle empty strings as well.

It would be safer to use this to ensure both are removed:

EXCEPT ( ..., { BLANK ( ), "" } )

[Sean]

Yes that did it - should have of thought of it!