Switch function count

[veakin]

Hi,

I am trying to make a count of the functions below. They all have the same characteristics as in they start with the same thing, as seen below.

I would like it to return count of accounts starting with A, B and C. So in this case 3, 3 and 3. 

I have the following code:

Accounts_TOP = VAR Top_Account_Name = External[TOP_ACCOUNT_NAME] RETURN SWITCH(TRUE(), Top_Account_Name in {"A1","A2","A3"}, "A") Top_Account_Name in {"B1","B2","B3"}, "B") Top_Account_Name in {"C1","C2","C3"}, "C")

[veakin]

Still doesn't return the desired info unfortunately.

The count measure above are counting on name. Right now I have the data in a pivot table with Name in rows and then in the values I have number/amount of value.  

[veakin]

Also it still wont accept the space between ""

SEARCH(" ",'Table'[name]&" ")

[veakin]

It doesnt work.... It return some completely wrong numbers..

The value function in my dataset is a count of the colomn, so like this:

Robot AS	3323
Robot AS	5433
Robot AS	9891
Robot OP	1011
Machine KJ	4123
Machine KJ	7532
Machine LL	1123
Machine LL	1301
VehicleHA	4311

And then by filtering on count of the code I get:

Robot AS	3
Robot OP	1
Machine KJ	2
Machine LL	2
VehicleHA	1

I should have stated this before... Sorry

[v-xiaotang]

Hi @veakin 

This scenario is easy, you can try this measure [count] and don't need to create other table,

count = CALCULATE(COUNT('Table'[name]),ALLEXCEPT('Table','Table'[name]))

If this answer helps, could you accept it as solution? Thanks! 🙂

Best Regards,

Community Support Team _Tang

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

[veakin]

Couldn't I make the count function just for the values starting with Robot e.g.? Cuz there are some values without space between, but they also only have one row in the dataset...

[veakin]

With above I could have a function only taking rows containing robot, machine and components if that makes sense and then if the rest of dataset could just stay the same and be shown at the same time.
So this table:

Robot AS	3
Robot BS	5
Robot KL	6
Robot OP	10
Machine KJ	4
Machine GH	7
Machine LL	11
Machine TR	130
VehicleHA	4

Becomes this:

Robot	24
Machine	152
VehicleHa	4

[v-xiaotang]

Hi @veakin 

In this scenario, you can use this,

NameList2 = DISTINCT(SELECTCOLUMNS('Table',"name",LEFT('Table'[name],SEARCH(" ",'Table'[name]&" ")-1)))

count2 = CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),LEFT('Table'[name],SEARCH(" ",'Table'[name]&" ")-1)=MIN('NameList2'[name]) ))

Best Regards,

Community Support Team _Tang

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

[veakin]

What should the Nonfoundvalue be then?

[v-xiaotang]

Hi @veakin 

Sorry, I didn't make it clear, I mean you can also add a NotFoundValue to check whether there is no space in some value in your data.

To solve the error, the solution is to clean your data firstly if there is no space in Name like bellow, 

Best Regards,

Community Support Team _Tang

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

[veakin]

Its like it sums of correctly but it doesn't distinguish between the values. So it just sums up to 700, without giving any labels.

[veakin]

Its the same?

I can't call the function with a space between "" in the search function.. it gives me 'Search wasn't found'

[v-xiaotang]

Hi @veakin 

There may be no space in some 'table' [name], check your data.

you can add a NotFoundValue in Search() to check.

If this still doesn't solve your problem, is it possible to share your sample file? we will check it.

Best Regards,

Community Support Team _Tang

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

[v-xiaotang]

Hi @veakin 

Is this result similar to what you want?

If yes, you can try this,

1. create a table NameList including names, here are 2 ways to create it,

(1) DAX

NameList = DISTINCT(SELECTCOLUMNS('Table',"name",LEFT('Table'[name],SEARCH(" ",'Table'[name])-1)))

(2) in PQ Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCspPyi9RcAxWitWBcZyQOd4+SBz/ADDHNzE5IzMvVcHbC4Xr7oHC9fFB4YYEgblhqRmZyTmpCh6OKFznMKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "name", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"name.1", "name.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"name.1", type text}, {"name.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"name.2"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"name.1", "name"}})
in
    #"Renamed Columns"

2. create the measure

count = CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),LEFT('Table'[name],SEARCH(" ",'Table'[name])-1)=MIN('NameList'[name]) ))

Best Regards,

Community Support Team _Tang

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

[veakin]

Hi,
Thanks for your reply!
The function doesn't seem to work for me.... 
I mean, I can see the function sums up to right amount. But when I plot the values with Name as row, it doesn't display the count as in the picture above.

Namelist = DISTINCT(SELECTCOLUMNS(Cars, "Name of Cars",LEFT('Cars'[NAME OF CARS],SEARCH("",'Cars'[NAME OF CARS])-1)))
Is my code wrong?

[v-xiaotang]

Hi @veakin 

Will it help?

Namelist = DISTINCT(SELECTCOLUMNS('Cars', "Name of Cars",LEFT('Cars'[NAME OF CARS],SEARCH(" ",'Cars'[NAME OF CARS])-1)))

Best Regards,

Community Support Team _Tang

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

[veakin]

Hmmm.. It doesn't seem to work.
The thing is, I have a table looking like this:

Robot AS	3
Robot BS	5
Robot KL	6
Robot OP	10
Machine KJ	4
Machine GH	7
Machine LL	11
Machine TR	130
Vehicle HA	4
Vehicle CV	17

And then I would like a table, that just counts like this:

Robot	24
Machine	152
Vehicle	21

[amitchandak]

@veakin , Try like

Accounts_TOP = VAR Top_Account_Name = External[TOP_ACCOUNT_NAME] RETURN SWITCH(TRUE(), left(Top_Account_Name,1) in {"A","B","C"}, left(Top_Account_Name,1))