Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
9 years ago

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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        OwenAuger
        Icon for Super User rankSuper User
        9 years ago

        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 )