cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Weird table creation for DAX gurus

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:

 

InterviewerContractor
Stephen FryStephen 
Tim McInnernyRik
Rowan AtkinsonRowan 
Stephen FryStephen 
Hugh LaurieHugh 
Rowan AtkinsonRowan 
Tony RobinsonTony 
Tim McInnernyRowan
Tony RobinsonTony 
Tim McInnernyTim 
Hugh Laurie 
Stephen FryStephen 
Hugh LaurieHugh 
Stephen Fry 
Miranda RichardsonMiranda 
Rowan AtkinsonRowan 
Rowan AtkinsonRik
Stephen FryStephen 
Tony RobinsonRowan
Tony RobinsonRik
Rowan Atkinson 
Tim McInnernyTim 
Rowan AtkinsonRowan 
Miranda RichardsonMiranda 
Miranda RichardsonMiranda 
Tony RobinsonTony 
Tim McInnernyRik
Tony RobinsonTony 
Rowan AtkinsonRik
Rowan Atkinson 
Tim McInnernyTim 
Miranda RichardsonMiranda 
Rowan AtkinsonRowan
Rowan AtkinsonRowan 
Miranda RichardsonMiranda 
Hugh LaurieHugh 
Rowan AtkinsonRowan 
Tim McInnernyTim 
Rowan AtkinsonRowan
Tony Robinson 
Tony RobinsonTony 
Tony RobinsonTony 
Tim McInnernyTim 
Tim McInnernyRik
Rowan AtkinsonRowan 
Tim McInnerny 
Rowan AtkinsonRowan 

 

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:

 

InterviewerContractor
Rowan AtkinsonRowan
Stephen FryStephen
Hugh LaurieHugh
Tony RobinsonTony
Tim McInnernyTim
Miranda RichardsonMiranda
 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 @scottsen 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.

3 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Weird table creation for DAX gurus

@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 ()
    )
)

UnionTable.png

* I just a Fred to testing.




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: Weird table creation for DAX gurus

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.

Highlighted
Super User
Super User

Re: Weird table creation for DAX gurus

Hi @KHorseman

 

I was just playing with this and saw your solution earlier Smiley Happy

 

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 )

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




10 REPLIES 10
scottsen Senior Member
Senior Member

Re: Weird table creation for DAX gurus

Well, that's a mess Smiley Happy

 

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?

Super User
Super User

Re: Weird table creation for DAX gurus

@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 ()
    )
)

UnionTable.png

* I just a Fred to testing.




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: Weird table creation for DAX gurus

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

 

@scottsenI 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.

Super User
Super User

Re: Weird table creation for DAX gurus

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.

Highlighted
Super User
Super User

Re: Weird table creation for DAX gurus

Hi @KHorseman

 

I was just playing with this and saw your solution earlier Smiley Happy

 

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 )

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: Weird table creation for DAX gurus

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

scottsen Senior Member
Senior Member

Re: Weird table creation for DAX gurus

What is this madness?

 

{ BLANK () }
Super User
Super User

Re: Weird table creation for DAX gurus

As always @OwenAuger's solution very clean and elegant Smiley Happy

@scottsenthat'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

Super User
Super User

Re: Weird table creation for DAX gurus

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 ( ), "" } )

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!