Creating Slice by using left 2 letters of column values

[TM_Evan]

I am attempting to create a slice for the left two characters of an existing order name field in SQL.

Example - FL-XX-X-XXXX would return FL

                 GA-xx-x-xxxx would return GA

This slice would be useful to toggle State for reports.

From what I thought I understand, Dax LEFT would be the way to go but I am not getting it right.

I am attempting to create a slice using the following steps

1) drag table to Page

2) Select Slicer from visualizer (This displays ALL existing order numbers and only way to slice is individual order number

3) Select New Measure and enter 'SliceNumber = LEFT(<table>,2)

Receive error - The syntax for 'table' is incorrect.

I am wondering if I need to add some grouping function to this measure but I am new to this and hopeful that the community may be able to assist me.

Thank you

[Greg_Deckler · Accepted solution]

LEFT takes a text value for its first parameter, not a table. Create a calculated column perhaps like:

Column = LEFT([column],2)

Then you can just use that column in your slicer.

[Greg_Deckler · Accepted solution]

LEFT takes a text value for its first parameter, not a table. Create a calculated column perhaps like:

Column = LEFT([column],2)

Then you can just use that column in your slicer.

[TM_Evan]

Thank you for the quick reply.

I think I understand what I am doing wrong.  I am creating this BI report from 2 joined table.  I am finding the measurement in the wrong table.  Could you please clarify to me the syntax?  I am using

[Name of Measurement] = LEFT ('Schema Table'[Column],2)

Still not working right.  Please continue pointing me in the right direction.  Thank you for all your help.

[Anonymous · Accepted solution]

Hi @TM_Evan,

I think you put these formula in a measure, right?

You can use calculate column to store these formula, then drag this calculated column to slicer.

In addition, if you only want to return the left 2 characters of chosen item, you can try to use below formula:

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

Regards,

Xiaoxin Sheng

[TM_Evan]

I discovered what I did wrong from the initial reply.  I failed to Add column and THEN use the DAX expression.  Once I did that I was able to use the Slice in the exact way that I needed it.  Thank you for all this excellent input.