Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
ingrid
Helper II
Helper II

check for relation with other table

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

9 REPLIES 9
v-lili6-msft
Community Support
Community Support

HI, @ingrid

     After my research, you can do these as below:

Assume that we have two table as below

table1

3.PNG

table2
4.PNG

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])

5.PNG

 

 

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

 

 

 

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

@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
Responsive Resident
Responsive Resident

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
Community Champion
Community Champion

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.

 

 





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

Proud to be a PBI Community Champion




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

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.





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

Proud to be a PBI Community Champion




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?

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

 

image.png





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

Proud to be a PBI Community Champion




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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.