How to check if a row exists on the Many side of One to Many with DirectQuery

[Anonymous]

Hi,

I have two tables. Table A is the One Side, Table B is the Many side.

I am trying to check (via a calculated column) if there exists a matching row with the Unique Key from Table A in Table B (where the Unique Key is a Foreign Key) for each row in Table A. 

As I am using DirectQuery, many of my options are limited. I can't use IN DISTINCT, FILTER, RELATEDTABLE... all these functions are not available for Power Query with a calculated Column.

Any suggestions?

Thanks

[amitchandak]

@Anonymous , direct query column has limitations.

In a table visual, have ID from both table, filter for A[ID] blank. Default is right missing id in dimension will come

or create a measure from B with +0 and filter measure =0. Value missing in B will come. Now it is left join

[Anonymous]

@amitchandak Thanks for your response.

I need the result in a Calculated column as a boolean (True if there is a match, false if not) as I will then be using this result to calculate another calculated column (which will then be used as a slicer).

I don't think FILTER is available in DirectQuery calculated columns. Is there some way to do this? I am new to DAX.

Thanks

[amitchandak]

@Anonymous , I doubt you can but let me check again. 

[Anonymous]

@amitchandak 
It's ok, I'll have to alter the database. I was hoping i'd overlooked something.

Thanks

[amitchandak]

@Anonymous , In my power Bi, On latest power bi version. I am bale to run related 

    Column = RELATED(Customer[Name])

1 - M (Column in sales on M side)

Filter is not working in Maxx , so this the only option

[Anonymous]

@amitchandak 

Unfortunately I can only run RELATED on the ONE side of the One-to-Many relationship. From what I read, it appears that you have to use RELATEDTABLE (which isn't even really what I want) on the MANY side. Intellisense doesn't even show any tables that are on the MANY side if using RELATED in the ONE side table (for a calculated column). Unfortunately you can not run RELATEDTABLE with DirectQuery. 

This is a bit of a severe limitation of Power Bi (if I am correct). I don't know how this limitation is in existence. It means you can not query a table that has a direct relationship with another one. Because you can't, it means you have to add duplicate columns to a table in the SQL backend. 

[amitchandak]

@Anonymous , Yes not all things are not supported. 

related I copy data from dimension to fact sales on many side.