Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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
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.
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |