Summarize and refer Group Names to Order Numbers

[Anonymous]

Hey guys,

I'm searching for a measure because I can't do it in the advanced editor any more.

I have an older dataset where it worked by creating new columns in the advanced editor, which should be good to demonstrate the problem:

This is an example of the raw data:

My goal is to show how many OrderNumbers are used by multiple teams,

E.g.:           First 4 rows: Team AAA and BBB worked together on one OrderNumber. (1111)

                    Row 5-8:     Team AAA,BBB, CCC and DDD worked together on one OrderNumber (2222)

And I also want to show how many OrderNumbers were used exclusively by one Team

E.g.:            Row 9:       Team CCC worked alone on one order Number (3333)

Here is the current workflow in power bi (different dataset):

Base data (but already modified that there is an amount of "UnderOrderNumbers" -> I don't want that modification)

This data is transformed to this:

 and this

Which ultimatley creates a dashboard that should look like that:

Is there a way to create a measure that can show it like that? I tried it, but I'm unfortunatley not capable of doing it (or maybe its not even possible..)

I'm thankfull for any help and ideas!

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Create a measure or column as a tooltip. See if it meets your needs.

Measure = 
CONCATENATEX(FILTER('Table',COUNTROWS(FILTER('Table',EARLIER('Table'[OrderNumber])='Table'[OrderNumber]))),'Table'[Team],"   ")

column = CONCATENATEX(FILTER('Table','Table'[OrderNumber]=EARLIER('Table'[OrderNumber])),'Table'[Team]," ")

Sample .pbix

If the problem persists,could you share the sample pbix via cloud service like onedrive for business?(Please mask any sensitive data before uploading)

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

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Create a measure or column as a tooltip. See if it meets your needs.

Measure = 
CONCATENATEX(FILTER('Table',COUNTROWS(FILTER('Table',EARLIER('Table'[OrderNumber])='Table'[OrderNumber]))),'Table'[Team],"   ")

column = CONCATENATEX(FILTER('Table','Table'[OrderNumber]=EARLIER('Table'[OrderNumber])),'Table'[Team]," ")

Sample .pbix

If the problem persists,could you share the sample pbix via cloud service like onedrive for business?(Please mask any sensitive data before uploading)

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

[Anonymous]

Thank you @V-lianl-msft that worked out pretty good!

But a new problem occured:

Since I linked the data for the team with another table over an many-to-many cardinality, I don't have the teams (AAA, BBB, CCC) but the Teammembers. Is there an easy way to get the Team names in the diagram or the DAX formula?

The "Replace" function or the "Substitute" function seem not good for this purpose since there are a lot of teammembers, which are changing here and there. 

[V-lianl-msft]

Hi @Anonymous ,

Share a sample pbix file or paste some data and tell the relationship between the tables. In this way, I can be more clear of the scenario so that I can help you better.

Please mask any sensitive data before uploading

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

[Anonymous]

Hi @V-lianl-msft

thank you for the fast reply.

I advanced you original pbix file to show the problem. I unfortunatley can't share data in onedrive through my company account, so I made screenshots, which hopefully help.  (Is there another way to upload the pbix file to the forum?)

Basedata

Teammember to groupnumber

Groupnumber to teamname

Relationships

The result (instead of the Teammembers, there should be the teamnames)

[Anonymous]

Thanks for your help in advance, really appreciate it! @V-lianl-msft 

[Anonymous]

Here are the tables to copy, I hope that makes it easier:

Basedata:

Teammember OrderNumber UnderOrderNumber tooltip

Harper	1111	1	Harper Harper Harper
Harper	1111	2	Harper Harper Harper
Harper	1111	3	Harper Harper Harper
James	2222	4	James James Mason Evely
James	2222	5	James James Mason Evely
Mason	2222	6	James James Mason Evely
Evely	2222	7	James James Mason Evely
Ella	3333	8	Ella Ella Ella Jackson Avery Jack
Ella	3333	9	Ella Ella Ella Jackson Avery Jack
Ella	3333	10	Ella Ella Ella Jackson Avery Jack
Jackson	3333	11	Ella Ella Ella Jackson Avery Jack
Avery	3333	12	Ella Ella Ella Jackson Avery Jack
Jack	3333	13	Ella Ella Ella Jackson Avery Jack
Scarlett	4444	14	Scarlett Madisom Eleanor
Madisom	4444	15	Scarlett Madisom Eleanor
Eleanor	4444	16	Scarlett Madisom Eleanor
Eleanor	5555	17	Eleanor Wyatt
Wyatt	5555	18	Eleanor Wyatt
Wyatt	6666	19	Wyatt
Harper	7777	20	Harper
Harper	8888	21	Harper
Jack	9999	22	Jack

Teammember Groupnumber

Harper	101
James	202
Mason	303
Evelyn	404
Ella	303
Jackson	202
Avery	202
Jack	404
Scarlett	303
Madisom	202
Eleanor	101
Wyatt	202
Carter	303
Graysom	404
Lily	404

Groupnumber Teamname

101	TeamA
202	TeamB
303	TeamC
404	TeamD

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Try:

team = RELATED('Group'[TeamName])

s_t = CONCATENATEX(FILTER('BaseData','BaseData'[OrderNumber]=EARLIER('BaseData'[OrderNumber])),'BaseData'[Team]," ")

Sample .pbix has been updated

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

[Anonymous]

Thank you @V-lianl-msft that worked out great so far.

The only question that I have left is, if there is any way to group the s_t outcomes in a good way.

Example:

I created a diagram out of your sample file to visualize my question.

"A A A" and "A" are the same Team that worked together, so I need it to be together in the diagram.

The Outcome in the table should be: "A" = 5 Order Numbers

So even if team A worked with different UnderOrderNumbers in one OrderNumber, it should still be counted like it would be if the team worked unter multiple OrderNumbers alone.

I hope it is understandable what I mean.

That logic should also be implemented for Team Cooperations:

For example there can be a "A B" outcome but not an "A A A B B" outcome.

Thanks for your help and excuse my bad explanation skills.

Kind regards

UserBi404

[Anonymous]

To maybe explain it better:

It should be checked for every OrderNumber, if one of the teams used it.

If yes, it should be counted one time,

if not, it shouldn't be counted.

I think the picture in my very first post in this thread can explain it better than my words. Thanks for your support @V-lianl-msft !

Edit.: I tried to do Datagroups, wit the "Group" option in PowerBi it kind of worked, but it is an never ending process of choosing all different possibilitys. Also it is not really dynamic, so if I add new data, I always have to start from scratch. Is there a way to group it automatically? 

[Anonymous]

I tried "Related"

column = CONCATENATEX(FILTER('Table','Table'[OrderNumber]=EARLIER('Table'[OrderNumber])),RELATED'Table2'[Teamnames]," ")

---

---

But it always shows me "Too many arguments were passed to the RELATED function. A maximum of 1 arguments are allowed for the function