Count id by lf using dax

[Anonymous]

Dear Friends,

i have table1(name,Bank) and contains multiple bank id(by line break(lf)) to indivdiual user as show in table:

 i need to count total bank id, for example, total bank id=11, and for Name8=3 and so on.

i did by power query using split by row(lf) and its working fine

but how can i get by using DAX ??

thanks

Jia

[Anonymous · Accepted solution]

Dear All

i solved myself the above problem, by:

len(bank id)-len(substitute(bank id,unichar(10),""))+1

Thanks

[v-rzhou-msft]

Hi @Anonymous ,

If your ids in one name is splicated by delimiters that occupy one character like space, you can try this code to count the bank id.

My sample:

Measure:

Count Bank id = 
VAR _ADDLEN = ADDCOLUMNS('Table',"Length",LEN('Table'[Bank id]))
VAR _ADDCOUNT = ADDCOLUMNS(_ADDLEN,"Count ID", VAR _MINLEN = 7 VAR _MOD = MOD([Length],_MINLEN) RETURN _MOD + 1)
RETURN
SUMX(_ADDCOUNT,[Count ID])

Result is as below.

Best Regards,
Rico Zhou

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

[Anonymous]

Dear 

i want to by direct Dax, not by Power Query because i have multiple data source and dont want to more complex

Here , total bank id is 11 because some name contains multiple bankd id by line break in bank id column.

[speedramps]

We want to help.

The answer I provided is in DAX!

However, you will need an input table with populated columns on each row. You will need to do that in power query with fill.

Please share elexample input data as a table not a screen shot. So we can import the data and build a solution.

Also provide example desired output and description.

Dont share private data.

[speedramps]

In power query use the Fill option to populate the Name on every row.

Create these Dax measures

Banks = COUNTDISTINCT(tablename[Bank])

Name8 banks = 

CALCULATE(

[Banks],

tanlename[Name] = 'Nanes8")

All banks =

CALCULATE([Banks],ALL())

Create a table visual with

Name, Banks, Name8 banks, All banks

Notice how the Calculate command overrides the row context. The Name8 banks is the same value for every row !!

Hope you understand?

Please click thumbs up and accept as solution.