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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
DenisBergeron
Helper I
Helper I

DAX new table base on two columns depends on condition

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
1 REPLY 1
v-xicai
Community Support
Community Support

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.

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.