Checking for record presence in distantly related table

[MarkPalmberg]

Hi.

I'm trying to create a measure that returns a value based on whether an ID and column value is present in a related table. I'm trying to emulate the solution from this post.

NameExists = if(CALCULATE(
	COUNTROWS(Table2),
	FILTER(Table2,Table2[FirstName] = EARLIER(Table1[Name] ) &&
		Table2[MiddleName] = EARLIER(Table1[Name])),
		FILTER(Table2,Table2[Reporting month] = Table1[Reporting Month])
	)
	 > 0,
	"Yes",
	"No"
)

Here's what my relationships look like:

I'd like to end up with a slicer that enables me to exclude values for Unit in the Prospect Pool table when the HOUSEHOLDLOOKUPID and Unit values match in the Household DQ Unit table. I also don't seem to be able to be able to get RELATEDTABLE to work when I attempt to create a calculated column on the Prospect Pool table:

DQ for Unit = IF(CALCULATE(
    COUNTROWS(FILTER(RELATEDTABLE('Household DQ Unit'),'Household DQ Unit'[UNIT]=

Thanks a lot for any thoughts you can spare! (Power BI Desktop for Report Server January 2019, FWIW.)

[v-xicai]

Hi @MarkPalmberg ,

Try to create column like DAX below, then let it as source of slicer visual.

DQ for Unit = IF(COUNTROWS(FILTER('Prospect Pool','Prospect Pool'[UNIT]='Household DQ Unit'[UNIT]&&'Prospect Pool'[HOUSEHOLDLOOKUPID]='Household DQ Unit'[HOUSEHOLDLOOKUPID]))>0,"Match","not Match")

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MarkPalmberg]

Thank you so much for this reply, @v-xicai . I think I may have tracked down my trouble. I get as far as:

DQ for Unit = IF(COUNTROWS(FILTER('Prospect Pool','Prospect Pool'[UNIT]=

and then autocomplete isn't populating the name of my Household DQ Unit table, which is a calculated table. Perhaps the calculated table is what's confounding the process? I apologize for not including that info in my original post. Here's the code for that table:

Household DQ Unit = 
CALCULATETABLE (
    SUMMARIZE(
        'Qualification Interactions',
        'Qualification Interactions'[HOUSEHOLDLOOKUPID],
        'Qualification Interactions'[BUSINESS UNIT]
    ),
    FILTER (
        'Qualification Interactions',
        'Qualification Interactions'[OUTCOME] IN {"Disqualified for Business Unit", "Disqualified for BI"}
    )
)

So now I'm wondering if there's a more efficient way to accomplish what I'm after. Thank you again for taking a look at this; I appreciate your time.

[v-xicai · Accepted solution]

Hi @MarkPalmberg ,

The calculated table Household DQ Unit won't affect the formula. Try to delete the relationship between table Household DQ Unit and HOUSEHOLDIDS , and create relationship between table Household DQ Unit and Prospect Pool, note that the Cross filter direction is still Both, which will treat the three tables as a single table. Then create column like DAX below.

DQ for Unit = IF(ISBLANK(LOOKUPVALUE('Prospect Pool'[UNIT],'Prospect Pool'[UNIT],'Household DQ Unit'[UNIT]))&& ISBLANK(LOOKUPVALUE('Prospect Pool'[HOUSEHOLDLOOKUPID],'Prospect Pool'[HOUSEHOLDLOOKUPID],'Household DQ Unit'[HOUSEHOLDLOOKUPID])) ,"not Match", "Match" )

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.