Group data by ID in PowerBI Service

[Anonymous]

I have a dataset where I want to group data by ID so that the data only count values for each ID once. I am not able to download nor open the data in the Desktop version, so need a way to cluster the data in Service. 

More details:

My dataset contains obervations on initiatives by different organisations, where each initiative expands over several rows - one for each category the initiative falls under. In a bar chart, I want to present how many initiatves are a result of a partnerships, and have added the value 1 for those initiatives that are partnerships. However, if an initiative expands over 5 categories, PowerBI will count this as 5 partnerships and not 1. How can I group the data by ID so that each inititaive is only counted once - without including ID in the chart as I want the oservations to be sorted by organization and not by ID? 

Thank a lot in advance!!

[GilbertQ]

Hi @Anonymous 

It sounds like you need to create a measure, which as it stands today you cannot create in the Power BI Service.

You should be able to use Power BI desktop to link to the same dataset and then create the measure?

[Anonymous]

Thank you for the answer, @GilbertQ 

I should be able to connect the dataset to through desktop. Also, the dataset is not that large so it can also be edited directly in the connected excel file.

I already have an ID that is a index number/measure for each initiative+ the name that is a sting and can also be used as ID. Will the index number help for the next step? 

[GilbertQ · Accepted solution]

Hi @Anonymous 

You could then use the DISTINCTCOUNT function to get it to only count the ID once?

[Anonymous]

Thank you, @GilbertQ! I solved it by using ID number instead of dummy to indicate the partnerships and then DISTINCTCOUNT. Appreciate your help!

[GilbertQ]

That is great news, thanks for letting us know!