Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Dynamically group

Hello,

 

I have a table with name of report, date of creation and creator.

 

I would like to group creator. I need them to keep their name if they create 5%  or more of the total pool of report. If they creadted less than 5% of report, they have to by group in "Other"

 

I can group them manualy but I will have to check evrey month if they are ine the right category

 

Do you know a way to do so dynamically?

 

Thank in advance,

 

Emmanuelle

 

1 ACCEPTED SOLUTION

Hi @Anonymous,

I would suggest that you create a table with only the creator names. This can be done by clicking Create Table and enter the following formula:

Creators = DISTINCT(Reports[Creator])

(assuming your table is called Reports.) Now establish a relationship between Creators and Reports on the Creator field.

 

Next step is to define a calculated new column called Group in the Creators table:

Group = 
VAR C = Creators[Creator] 
RETURN 
IF(CALCULATE(COUNTROWS(Reports),Reports[Creator] = C)/COUNTROWS(Reports) > 0.05, 
C,
"Other")

In your table visual you can now use Creators[Group] instead of Reports[Creator] and you should get the desired result.

 

View solution in original post

4 REPLIES 4
vanessafvg
Super User
Super User

@Anonymous you can use a switch statement probably to derive it, however can you show me what your data looks like?  otherwise it will be hard to show you how to write it





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

Hi Vanessa,

 

My data look like that

example.png

 

I have the creator name, the date of creation and the raport title

 

I would like to group C and D because they produce less then 5% of the report total

 

Hi @Anonymous,

I would suggest that you create a table with only the creator names. This can be done by clicking Create Table and enter the following formula:

Creators = DISTINCT(Reports[Creator])

(assuming your table is called Reports.) Now establish a relationship between Creators and Reports on the Creator field.

 

Next step is to define a calculated new column called Group in the Creators table:

Group = 
VAR C = Creators[Creator] 
RETURN 
IF(CALCULATE(COUNTROWS(Reports),Reports[Creator] = C)/COUNTROWS(Reports) > 0.05, 
C,
"Other")

In your table visual you can now use Creators[Group] instead of Reports[Creator] and you should get the desired result.

 

Anonymous
Not applicable

Perfect, thank you very much

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors