check for relation with other table

[ingrid]

I want to check if there is one (or more) row(s) in another table (table2), with a relation to the first table. I've found a soltion which I've tryed in my ow model but now I get the error:

- The functoin calculate is not allowed as part of a calculated column DAX expressoin in DirectQuery modus.

Is there maybe a workaround for this?

FactuurJaNee = CALCULATE(COUNTROWS(FeitFactuurregel); FILTER(FeitFactuurregel; VALUE(FeitFactuurregel[xToewijzingnummer]) = VALUE(FeitLeveringActueel[xToewijzingnr]))) > 0

[v-lili6-msft]

HI, @ingrid

     After my research, you can do these as below:

Assume that we have two table as below

table1

table2

and then we can use LOOKUPVALUE Function to check whether there are values in the column of table1 exist in the column of table2 like below:

Column = LOOKUPVALUE(FeitFactuurregel[xToewijzingnummer],FeitFactuurregel[xToewijzingnummer],FeitLeveringActueel[xToewijzingnr])

If it is not your case, please share some simple data sample and expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Best Regards,

Lin

[ingrid]

@v-lili6-msft: Sorry for the delay, this is the situation, but now I get the error: 

The function LOOKUPVALUE is not available als part of de calculated column DAX-expression in DirectQuery models.

[Johanno]

Hi, you could get a list of all records that exists in both tables if they are related with:

Number of rows in the related table = CALCULATE(COUNTROWS('Table1');RELATEDTABLE('Table2'))

Would that work for you?

[PattemManohar]

If there is a relationship between those two tables and you just want to identify the number of records related per key, then you can simply drag the Primary Key field from Table1 and any field from Table2 on to a table visual. By default, it sums up the field (if it is numeric) from table2 then you can change it to "Count" under values section of the visual.

[ingrid]

Hi @PattemManohar, there is a relationship between the tables but not for all records in table 1 (FeitLeveringActueel) there is actually a (or more) row(s) in table 2 (FeitFactuurregel).

[PattemManohar]

Yes, if there is no matching record found in the child table then you can't see the parent key in the counts.

To handle this, I've tried with below steps:

1. Merged both Parent and Child tables into a new table (Left Outer Join -- Parent on Left), where you can see the NULL values for non-matching in child table. 

2. Add a conditional column on the child table key field, if it is null 0 else 1, I call it is as "Check" (Make sure it is numeric data type)

3. Use this new table which contains both parent and child fields along with the new field added in above step (make sure you can expanded the fields after merging). Drag ParentKey filed and Check field (change the value type to "SUM") 

Then it will show the count as 0 for non-existance keys in child table.

[ingrid]

Hi @PattemManohar, I'm a newbie so most certainly I'm doing something wrong, but for now I don't have the 'Create table' available. May it's because I',m working in the DirectQuery-modus? Is there a(nother) way to get this option available?

[PattemManohar]

Under Power Query Editor, you can see the Merge Queries option... as below

[ingrid]

thanks @PattemManohar, I found it.

It appears that the fields aren't of the same type.

Which is strange, because as far as I can see they both are numbers.

I'm gonna find out whats wrong and then try the rest of the steps.