topic Re: Certain UNICODE characters break measure results. (in a weird way, too) in DAX Commands and Tips

Certain UNICODE characters break measure results. (in a weird way, too)

nchr — Sun, 12 Feb 2023 08:09:25 GMT

Hey everyone, I have this simple piece of code that displays a number of star symbols according to the score for an entity (it's companies in my case).

TEST STARS = VAR score = INT([Company Score]*5) VAR stars = REPT(UNICHAR(11088), score) RETURN stars

I have multiplied by 5 to make the issue totally clear: so, for a score of 3, I would expect to see INT(3*5) = 15 stars, right?

Well, power bi says "WRONG":

Now, watch this, the weird part: if I add the score in the measure's return value, i.e. changing the last line to :

RETURN score & stars

then I get the correct number of stars:

I also get the correct number of stars if I use a different star symbol, for example UNICHAR(9733).

I have spent hours debugging the wrong number of stars shown, before I realized the cause - and now, I want to get to the bottom of this.

Any ideas why this is happening? Should it be reported as a bug?

Thank you all

UPDATE1: It seems unichars up to 11034 work fine; from 11035 and onwards, the weird behaviour is happening.
UPDATE2: The cause is the existence of Calculation Groups in the model, even though not used in the offending visual.

Re: Certain UNICODE characters break measure results. (in a weird way, too)

OwenAuger — Sat, 11 Feb 2023 21:46:42 GMT

Hey nchr

I have tried to reproduce the issue at my end with similar Compay Score and TEST STARS measures, but I can't reproduce it I'm afraid.

I get the correct number of stars whether using UNICHAR 11088 or 9733.

Do you have a PBIX you could share that demonstrates the issue (sanitised if necessary)?

Regards

Re: Certain UNICODE characters break measure results. (in a weird way, too)

nchr — Sun, 12 Feb 2023 08:10:44 GMT

OwenAuger thank you for looking into this, it was driving me crazy!

As I was cleaning the model to send it here, I found out the cause, but not why it happens: it is the Calculation Group.

Although the calculation group is not used at all in the visual, it's presence in the model causes this weird behaviour. When I deleted the calc group, the star measure worked as expected.

Check it out yourself with the attached model, if you explain that weird behaviour I will find peace of mind 😉

Well, I could not find a way to attach the file here, so here is a WeTransfer link:
https://we.tl/t-lvEn7V60bI

Re: Certain UNICODE characters break measure results. (in a weird way, too)

OwenAuger — Sun, 12 Feb 2023 12:42:02 GMT

nchr thanks for uploading the file 🙂
(Super User status is needed to be able to attach directly to a post.)

Thanks for your additional observations, which helped investigate this. This is very odd behaviour!

At this stage I can say:

This behaviour does appear to a bug.
I can't see any valid explanation for the behaviour, and I can't explain it in terms of the DAX engine. Hopefully someone from the Power BI team can look into this and explain.
The behaviour occurs in Power BI visuals (or equivalent DAX queries) but not in a PivotTable connected to the Power BI dataset using Analyze in Excel. This suggests that it is a DAX-specific issue, since Excel generates MDX queries. Presumably any other client tools using MDX would not exhibit the bug.
The conditions required to see this behaviour (from my testing) are:
1. At least one calculation group is present in the model.
2. The measure being visualized returns a text value by repeating a single character multiple times.
  This can be done with the REPT function or CONCATENATEX/GENERATESERIES.
3. More than one "row" of results is returned in the visual.
  If just one CompanyID is filtered for example, the STARS measure returns the correct result.
4. Only certain characters result in this behaviour.
  In my testing, characters in the range 11044-11512 mostly cause the bug, with some exceptions (e.g. 11400 & 11480).
  Characters outside the range 11044-11512 seem fine
Workaround:
The only method I can get working is to include an additional "invisible" character in the repeated string, such as UNICODE(8203). This is fine from a visual perspective, but still unsatisfying in that we have to add "redundant" characters.

STARS fix = VAR score = INT( [SCORE] ) VAR stars = REPT( UNICHAR( 11088 ) & UNICHAR( 8203 ), score ) RETURN stars

Feel free to log this as a bug. I plan to do the same through formal/informal channels.

I'd be interested to learn if you find any other workarounds or explanations yourself.

Regards,

Re: Certain UNICODE characters break measure results. (in a weird way, too)

OwenAuger — Mon, 13 Feb 2023 22:54:29 GMT

Update: I have posted this to the Issues forum:

https://community.powerbi.com/t5/Issues/Text-measure-returns-incorrect-value-under-certain-conditions/idi-p/3076939

Re: Certain UNICODE characters break measure results. (in a weird way, too)

Mi_80 — Thu, 12 Oct 2023 10:13:54 GMT

I am having a similar issue.

I have a simple measure:

Var maxcol = CALCULATE(MAX('Table (2)'[Start Date]), ALLEXCEPT('Table (2)', 'Table (2)'[Placeholder ID]),

'Table (2)'[Start Date] <= Today()) RETURN IF ('Table (2)'[Start Date] = maxcol , "True","False")

If I change True to say "Fish" it still works and returns fish where true, but if I change it to other words eg 'Dog' it doesn't caluclate correctly and gives a false were a true or 'dog' should be.

How does simply changing the string impact the measure in this way?