calculate total DAX issue

[baneworth]

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

[v-cgao-msft · Accepted solution]

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]

Hey @v-cgao-msft,

Thank you, appreciate it a lot