DAX makes me crazy : CONVERT

[HC-AutomateUser]

Good evening CET !

You cannot see it but I am crying right now. I am 49, has almost developped anything since 35 years, but today... Today... I am about to jump from the 2nd floor (you know : not to die but because I need that hurts to feel alive).

Why ? Just because of DAX. I have never had a full-day with absolutely nothing that worked. The basic fro the basic commands did not want to work.

Even a simple DateAdd() does not ! Can you imagine !!!

But here is why I post this bottle to the sea... I have opened a Power BI report to make some changes. It has always worked... But if I edit a table definition, it falls down.

I have created a new table this way :

ConsolidatedTables =

VAR Mvt =

SELECTCOLUMNS(

'tableName1',

"RefProduct", CONVERT('tableName1'[RefProd], STRING),

"Channel", CONVERT('tableName1'[CDV], STRING),

(...)

"mQtt", 'tableName1'[Qtt]

)

VAR Cont =

SELECTCOLUMNS(

tableName2,

"RefProduct", CONVERT('tableName2'[Réf. ouvrage], STRING),

"Channel", CONVERT('tableName2'[Canal de vente:Title], STRING),

(...)

"cMargin", 'tableName2'[Marge]

)

VAR Result = FILTER(NATURALLEFTOUTERJOIN(Mvt, Cont), [Channel] <> "n/a" && [mCreated] >= [cFrom] && [mCreated] <= [cTo])

RETURN Result

I have 2 issues here (please remember that it works until I edit it) :

CONVERT('tableName1'[RefProd], STRING)

It sounds like STRING is not recognized anymore. Does the syntax has changed ??? In Microsoft's documentation, it should still work.

VAR Result = FILTER(NATURALLEFTOUTERJOIN(Mvt, Cont), [Channel] <> "n/a" && [mCreated] >= [cFrom] && [mCreated] <= [cTo])

I have a JOIN error that says the types of RefProduct is not the same in both tableName1 and tableName2. How is it possible ? Who changed the types of my data ? Give me a name !!! ...Oh, of course, maybe that the error message is wrong...

So please help ! I have built dozens of Power BI dashboards, It cannot be possible that some (or all ???) of them can break just when I try to change one.

Does someone has already met this issue and do you have any advice that could help ?

Many thanks by advanced.

B.R.

Eric 

[CNENFRNL]

I'm wondering why you cling to DAX. It seems an easy mission in PQ. In fact, the complexity of NATURALLEFTOUTERJOIN under the hood is way much more than it appears,

NATURALLEFTOUTERJOIN – DAX Guide

[HC-AutomateUser]

Hello,

Many thanks for your answer. Why I clinged to DAX ? Just because it exists and I wanted to try. It worked with no real issue until today. To say the truth, it still works until I edit the piece of script (and if I do that, as I explained, both Convert() and Naturalleftouterjoin go to errors).

For the "join", I find it really like in SQL requests.

I did not know Power Query. I see it is a low / no code interface... I do not like that because it is full of traps (at least if one could code in C# inside !!!) and so complex to configure and slow to execute when you have something a little bit complex to do (it often take hours to do something new in Power Automate), but I will try it, thanks for the advice !

UPDATE : OK, I did not know that Power Query was the thing I also already use in Power BI, so that will not help.

Just one thing I have not said, it is that the source is a SharePoint list and not a database, I do not know if this is something that may have an impact or not... But never mind, I will try something else.

Eric

[CNENFRNL]

I once did what you do now, considering DAX as a cure-all until I found PQ (the Mashup language to be exact) is another caviar in PBI.

Like what you mentioned, PQ desguises with a low/no code UI😀; but its real power lies in its script editor. At least 90% of complicated missions of data ETL are to accomplish there.

[HC-AutomateUser · Accepted solution]

Hello again 🙂

Finally I made all work this morning at 4AM (CET). The

"attribute" = CONVERT(column, STRING)

has been replaced by a

"attribute" = column & ""

...Something I use to do when I do not have the choice to use a var typed variable. Not clean but it works.

I had a second issue coming from 2 other columns (one in each SharePoint list) that are search typed, with the same source. As I explained, it works until I edit the DAX script but I needed to change something in it, so I took 2 attributes with a different name (instead of the same one to be taken into account by the NATURALLEFTOUTERJOIN), and I added a FILTER on [attribute1] = [attribute2].

Now I can finish the v2 of my dashboards.

Many thanks, again, for your advice about Power Query. Be sure this is something I will work on once the reports will be published (even if I do not eat caviar anymore since I discovered the female fishs were opened alive to get the eggs, then thrown back into the sea still alive but dying, thanks to humanity) !

Thanks again !

B.R.

Eric