Forum Discussion

baneworth's avatar
baneworth
Icon for Helper III rankHelper III
3 years ago
Solved

calculate total DAX issue

Hey Community

 

If you could take a look i would appreciate it.

 

Problem:

I get Location Data (SubLoc) in form of single line of text like PM1 or PM1,PM2 or PM1,PM2,PM3,PM4 and so on.....

Now each PM (Process Module) should get counted individual, in case of PM1 = 1, PM1,PM2 = 2,....

 

Therefore i setup an excel file with all possible outcomes and assigned the count.

 

Now after importing it seems the created count does not get auto calculated. I am struggling with the correct DAX now to get the total of count.

 

 

I thought that the ID can be used with the count somehow.

 

Any input is welcome to get the total of count row

Thank you

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi baneworth ,

    My steps are to go to Power Query Editor first. Select the [SubLoc] column and split the column.

    Select these four columns and unpivot the columns.

    Then new two measures:

     

    SubLoc = CONCATENATEX(FILTER('Table','Table'[Serial Number]=MAX('Table'[Serial Number])&&'Table'[ID]=MAX('Table'[ID])&&'Table'[Loc]=MAX('Table'[Loc])),'Table'[Value],",")
    Count = COUNT('Table'[Value])

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi baneworth ,

    My steps are to go to Power Query Editor first. Select the [SubLoc] column and split the column.

    Select these four columns and unpivot the columns.

    Then new two measures:

     

    SubLoc = CONCATENATEX(FILTER('Table','Table'[Serial Number]=MAX('Table'[Serial Number])&&'Table'[ID]=MAX('Table'[ID])&&'Table'[Loc]=MAX('Table'[Loc])),'Table'[Value],",")
    Count = COUNT('Table'[Value])

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

    • baneworth's avatar
      baneworth
      Icon for Helper III rankHelper III
      3 years ago

      Hey Anonymous,

       

      Thank you, appreciate it a lot