Filtering and new columns

[Pmorg73]

Hi. Newbie question

I have a dataset with lots of job numbers. Some of these are prefixed with a J or a P. Up to now I have been slicing or filtering the visuals with this "starts with" P or J. And that was working great.

I now want to do visuals comparing the normal jobs to the prefix ones, ie bar charts of total compared to the P and J jobs.

I assume I may need a new column? But how do I then tell PowerBI to select only the values starting with J. I tried calculate but could not get the filter to select all that start with P for example.

Thanks

Phil

[v-juanli-msft · Accepted solution]

Hi @Pmorg73 

you can write your measure like so:

Measure P = CALCULATE(SUM('Table'[Sales]),FILTER('Table',LEFT('Table'[Product],1)="P"))
Measure J = CALCULATE(SUM('Table'[Sales]),FILTER('Table',LEFT('Table'[Product],1)="J"))

Measure SUM not include P & J =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        'Table',
        LEFT ( 'Table'[Product], 1 ) <> "P"
            && LEFT ( 'Table'[Product], 1 ) <> "J"
    )
)

Measure SUM = SUM('Table'[Sales])

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pmorg73]

thanks Maggie, perfect. I had deduced I needed the LEFT, but had not yet seen FILTER. Kinda obvious once you see it.