Left 2 characters from column - Direct Query

[Anonymous]

Hi there,

I'm using Direct Query and want to create a slicer / report based on the left 2 characters of a column.

Example source table:

Code          Claim

AZ12          X

AB123       X1

AZ23         X3

Requested Report:

Left 2 Code        Amount Claims

AZ                     2

AB                      1

I'm using the following DAX for left 2 characters. When I get rid of the column Code, also the Left 2 Code measure goes empty. Any tips on solving this? I cannot add a column because of direct query.

Selected 2 character = 
var selected= if(HASONEVALUE('Table'[Column]),VALUES('Table'[Column]),BLANK())
return
If(selected<>BLANK(),LEFT(selected,2))

[v-eqin-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.

Best Regards,
Eyelyn Qin

[v-eqin-msft]

Hi @Anonymous ,

According to the official document, HASONEVALUE() returns TRUE when the context for columnName has been filtered down to one distinct value only. Otherwise is FALSE.

In addition, calculated columns could not be dynamic that means values won't change according to the user selection. So It's not appropriate to use HASONEVALUE().

Please take a look at the below screenshot to understand the difference between Column and Measure.

In summary, as @amitchandak suggest , please just use LEFT() function directly.

Here is the pbix file.

Best Regards,
Eyelyn Qin

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

[amitchandak · Accepted solution]

@Anonymous , if it is a new column simply use

 LEFT('Table'[Column],2)