DAX new table base on two columns depends on condition

[DenisBergeron]

I have two table with the same double key logic :

| keyA  | keyB | Data   |
+-------+------+--------+
|  1    | null | blabla |
|  2    | bob  | this   |
|  null | bill | that   |
|  null | mike | does   |

In both table is the [keyA] is not present (null) I should take the [keyB]

Because of the size of the Data I use Direct Query and not Import Query
I first try to create a new column with

UniqueKey = if(ISBLANK(FirsTable[KEYA]),FirsTable[KEYb], FirsTable[KEYA])

Then when I try to make a relationships with both uniqueKey in both table, PowerBi freeze 2gb of ram, 80% CPU during 1h before I kill it.

I think PBI try to load every rows in both table to make a match.

So I think to myself, and if I do a reference table like in 1980 with old SQL many to many.

I suppose that this time, it will load one table at the time to create the second one.

And after that I will be able to create a second reference table with the second table.

And then link them together....

But then, how to created this kind of table ? I didn't find nothing usefull.

I try this :

from the UniqueKey I try created another table like this

KeyFromTableA = DISTINCT(TableA[UniqueKey])

Then I receive this error :

Conversion failed when converting from a character string to uniqueidentifier.. The exception was raised by the IDataReader interface.

I try copying my table with M, loading only the two columns with SQL creating a single Key same problem.

I try another SQL thing

SELECT COALESCE(GlobalId, Name) AS KeySA 
               FROM [DataWarehouse].[dbo].[TableB] 
               WHERE  Site ='BlaBla.com' 
                    AND SnapshotId IN (SELECT TOP (1) SnapId  FROM [Warehouse].[dbo].[Snap] ORDER BY Date DESC)

And got this error for one of my two table :

DataSource.Error: Microsoft SQL: Conversion failed when converting from a character string to uniqueidentifier.
Details:
    DataSourceKind=SQL
    DataSourcePath=DataWarehouse
    Message=Conversion failed when converting from a character string to uniqueidentifier.
    Number=8169
    Class=16

[v-xicai]

Hi @DenisBergeron ,

You may refer to the similar cases: case1, case2 case3 ,case4.

If you still have this issue for Power BI, you'd better create a support ticket in Power BI Support to get further help.

Best Regards,

Amy

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