cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DenisBergeron Frequent Visitor
Frequent Visitor

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
Community Support Team
Community Support Team

Re: DAX new table base on two columns depends on condition

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 47 members 842 guests
Please welcome our newest community members: