Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count id by lf using dax

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

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Dear All

     

    i solved myself the above problem, by:

     

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

     

    Thanks

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.

     

     

  • speedramps's avatar
    speedramps
    Super User
    4 years ago

    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.
     
     
     
     
     
     
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      speedramps
      Super User
      4 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Dear All

     

    i solved myself the above problem, by:

     

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

     

    Thanks