Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Left 2 characters from column - Direct Query

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))

 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , if it is a new column simply use

     LEFT('Table'[Column],2)

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , if it is a new column simply use

     LEFT('Table'[Column],2)

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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