topic Re: Direct Query: Remove characters from beginning Dax in DAX Commands and Tips

Direct Query: Remove characters from beginning Dax

Anonymous — Mon, 01 Jun 2020 10:43:13 GMT

I'm trying to remove the first 7 characters from a period list to leave the remaing 6 behind e.g. "Period 1 - 2018/19" to just 2018/19.

I'm working with direct query and have created a new column for this but it doesnt seem to work:

Sort Year =

RIGHT (

LEFT (

'looker_views lkr_Questionnaire'[Questionnaire_Title_Secondary],

SEARCH ( "_", 'looker_views lkr_Questionnaire'[Questionnaire_Title_Secondary],1, LEN ( 'looker_views lkr_Questionnaire'[Questionnaire_Title_Secondary]) - 1 ) - 1

LEN (

LEFT (

'looker_views lkr_Questionnaire'[Questionnaire_Title_Secondary],

SEARCH ( "_", 'looker_views lkr_Questionnaire'[Questionnaire_Title_Secondary],1, LEN ( 'looker_views lkr_Questionnaire'[Questionnaire_Title_Secondary]) - 1 ) - 1

)

) - 7

)

Re: Direct Query: Remove characters from beginning Dax

HotChilli — Mon, 01 Jun 2020 13:20:22 GMT

It throws an error? Or does nothing? Or returns an incorrect result?

Can you clear that up and also provide a representative data sample. What's the reason for not using a simpler formula to extract the 7 chars on the right?

Re: Direct Query: Remove characters from beginning Dax

Anonymous — Mon, 01 Jun 2020 13:54:12 GMT

It throws an error: Function: 'ISERROR' is not allowed as part of a calculated column DAX expressions on DirectQuery Models

Here's a sample dataset of the data I'm working with (Note is has some periods in reverse too):

https://app.powerbi.com/groups/me/reports/7f8586b7-1789-48d2-9866-d9434547dd26?ctid=0727f0b0-9d54-4244-a73c-2debe1367db8

From other responses it seems to be a limitation of Direct Query:

https://community.powerbi.com/t5/Desktop/Trim-the-first-7-characters-from-value/m-p/1133084#M515998

Re: Direct Query: Remove characters from beginning Dax

HotChilli — Mon, 01 Jun 2020 14:17:14 GMT

I think you have 2 different forms of similar question floating around.

Also I can't access the app.powerbi link provided (which is quite right)

Could you paste the data sample here? Here's how

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Re: Direct Query: Remove characters from beginning Dax

Anonymous — Mon, 01 Jun 2020 14:30:54 GMT

Sounds about right, just stuck for time on this one unfortunately so I cast the net out wide.

Here's the sample data:

Questionnaire_Title_Secondary	% of Total Head Delivered
2017/18 - Period 1	0.58%
2017/18 - Period 10	0.42%
2017/18 - Period 2	0.57%
2017/18 - Period 3	0.54%
2017/18 - Period 4	0.20%
2017/18 - Period 5	0.15%
2017/18 - Period 6	0.19%
2017/18 - Period 7	0.13%
2017/18 - Period 8	0.28%
2017/18 - Period 9	0.22%
Period 1 - 2018/19	0.59%
Period 1 - 2019/20	1.77%
Period 1 - 2020/21	0.70%
Period 10 - 2018/19	0.76%
Period 10 - 2019/20	1.11%
Period 10 - 2020/21	0.66%
Period 11 - 2018/19	1.52%
Period 11 - 2019/20	0.91%
Period 11 - 2020/21	1.32%
Period 12 - 2018/19	0.99%
Period 12 - 2019/20	0.50%
Period 12 - 2020/21	1.85%
Period 2 - 2018/19	0.50%
Period 2 - 2019/20	0.52%
Period 2 - 2020/21	1.66%
Period 3 - 2018/19	0.71%
Period 3 - 2019/20	1.34%
Period 3 - 2020/21	0.52%
Period 4 - 2018/19	1.40%
Period 4 - 2019/20	0.33%
Period 4 - 2020/21	0.86%
Period 5 - 2018/19	0.60%
Period 5 - 2019/20	0.84%
Period 5 - 2020/21	0.71%
Period 6 - 2018/19	0.34%
Period 6 - 2019/20	1.01%
Period 6 - 2020/21	0.87%
Period 7 - 2018/19	0.86%
Period 7 - 2019/20	0.55%
Period 7 - 2020/21	1.17%
Period 8 - 2018/19	0.98%
Period 8 - 2019/20	0.77%
Period 8 - 2020/21	1.21%
Period 9 - 2018/19	0.46%
Period 9 - 2019/20	0.83%
Period 9 - 2020/21	1.02%

I'm just trying to extract the years in 2018/19 format. There are other values in this column but I only need the ones which contain "/" i.e. Period 9 - 2020/21

Re: Direct Query: Remove characters from beginning Dax

Anonymous — Mon, 01 Jun 2020 15:18:46 GMT

HotChilli

I have this substitute query that seems to work but the ordering is the issue now i.e. Period 9 - 2020/21 vs. 2017/18 - Period 5

Query:

Sort Year = LEFT(

RIGHT('looker_views lkr_Questionnaire'[Questionnaire_Title_Secondary],8)

,8)

I have this table:

2018/19	9.71%
2019/20	10.48%
2020/21	12.55%
eriod 10	0.42%
Period 1	0.58%
Period 2	0.57%
Period 3	0.54%
Period 4	0.20%
Period 5	0.15%
Period 6	0.19%
Period 7	0.13%
Period 8	0.28%
Period 9	0.22%

Results are from the sample data I previously sent

Re: Direct Query: Remove characters from beginning Dax

HotChilli — Mon, 01 Jun 2020 17:22:02 GMT

Ah, you've lost me there. I don't understand what you're telling me.

This is the simplest I could come up with (not tested on Direct Query)

ColumnText2 = IF (LEFT(TableX[theColumn],1) IN {"1", "2"} , LEFT ( TableX[theColumn], 7) , RIGHT ( TableX[theColumn], 7))

May I also ask what the source system is because Power Query can do this if the source is SQL Server or a few others?