Weird table creation for DAX gurus

Anonymous

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.

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.

Hi Anonymous

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 )