please help with using LEFT function inside the DAX

[flora1]

I have this =CALCULATE([Sum of Amount], FILTER(DATA, LEFT(VALUES(DATA[Serial]),1)=5)) i am trying to check the condition. but it does not work.

please help me figure out how to use the left function in DAX

 

[Ozeroth]

Hi flora1,

I can see two fixes to make here:

1. You don't need the VALUES function. Since FILTER iterates through the rows of DATA, you can use a direct reference DATA[Serial] to refer to the value on the current row. However, using VALUES gives you a table of all DATA[Serial] values visible in the filter context where the measure is evaluated, which could be multiple values.
2. You should enclose 5 in quotes since you are comparing it with a text value

One solution is to keep your FILTER function as-is but remove VALUES:

=CALCULATE ( [Sum of Amount], FILTER ( DATA, LEFT ( DATA[Serial], 1 ) = "5" ) )

You can also write something like this, which I would prefer since it applies a filter only on the DATA[Serial] column rather than iterating/filtering the DATA table:

=CALCULATE ( [Sum of Amount], KEEPFILTERS ( LEFT ( DATA[Serial], 1 ) = "5" ) )

KEEPFILTERS ensures that the DATA[Serial] filter intersects with any other filters applied which should be in keeping with the intent of your original measure. You could probably omit KEEPFILTERS as long as you aren't applying any other filters that involve DATA[Serial].

Regards,
Owen