DAX column can't count total incidents of a number in columns when the column type is text

[julesdude7]

Hi there,

I have a Power BI report. One of the tables in my data model is a little troublesome for me. It is loaded from a SharePoint list. It looks a bit like this, here's one row as an example

AM Route Mon	PM Route Mon	AM Route Tue	PM Route Tue	AM Route Wed	PM Route Wed	Total Shifts
300	150	OW	AB	300	150

The route columns either contain a route shift number, or they contain letters which means it is not a route and should not be counted as a shift (with the exception of 'OW').

What I want to do is create a calculated DAX column for Total Shifts. This needs to show the total amount of route shifts that were undertaken for the days in the week as displayed in the other columns. For each day of the week, if the AM or PM routes show a number or show 'OW' then this should be counted as 1 shift. If they contain anything else, even a blank, then this should not be counted as a shift. So the total for the row above should show 5 shifts.
The trouble I've run into with this is regarding data types. The columns for the AM and PM routes are text, and as far as I know and understand, I can't get use VALUE in DAX to treat this text data type as a value so that it can identify if the route is a number and should therefore be counted.
So how can I do this?  

[julesdude7]

Hi @v-tianyich-msft 

Thanks for this. Unfortuantely unpivoting won't work for this table due to the fact that the SharePoint list it derives from is structured by a PowerApps system which inputs the data in weekly rows. There is too much repetition of data (in columns not in the above example I provided) if we unpivot in this way.

[v-tianyich-msft]

Hi @julesdude7 ,

You can create a new query and remove unnecessary columns after unpivot. Because only date and value columns need to be used only.

Best regards,
Community Support Team_ Scott Chang

[v-tianyich-msft]

Hi @julesdude7 ,

This doesn't seem to be possible with DAX, but I can provide you with M Code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjYwUNJRMjQFkf7hQMLRCUggRGNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"AM Route Mon  " = _t, #"PM Route Mon  " = _t, #"AM Route Tue  " = _t, #"PM Route Tue  " = _t, #"AM Route Wed  " = _t, #"PM Route Wed  " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"AM Route Mon  ", Int64.Type}, {"PM Route Mon  ", Int64.Type}, {"AM Route Tue  ", type text}, {"PM Route Tue  ", type text}, {"AM Route Wed  ", Int64.Type}, {"PM Route Wed  ", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Value", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if Text.Select([Value],{"0".."9"})=[Value] or [Value] ="OW" then 1 else 0)
in
    #"Added Custom"

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.