Problem with visuals displaying the first N values ​​and a dynamic "other" group

[noviceBI]

Hello everyone,

I am a beginner on Power BI, and I am looking to create a donut chart displaying the number of incidents for each process in my company, knowing that only the 7 processes with the most incidents should appear on the chart, with all the others grouped in an "other" category, and this in a dynamic way.

Thanks to a community user, I was able to test the following solution:

1/ 1/ Create a disconnected table listing all processes with a line for « Autres »
Processus avec autres =
VAR _Processus = ADDCOLUMNS(DISTINCT('Incidents 2024'[Processus]),"Tri",BLANK())
VAR _Autres = ROW("Processus","Autres","Tri",1)
Return UNION(_Processus,_Autres)

2/ Rank the processes relative to each other based on the number of incidents
Rang_Processus = RANKX(ALL('Incidents 2024'[Processus]),[Total_Incidents],,DESC,DENSE)
With Total_Incidents = COUNT('Incidents 2024'[Incident]) (each incident is identified by a code in the column "Incident")

3/ Create another measure to link the ranking to the created table
Top 7 incidents avec autres =
VAR CumulIncidents = SUMMARIZECOLUMNS('Incidents 2024'[Processus],"@Rang",[Rang_Processus],"@Incidents",[Total_Incidents])
VAR IncidentsCategorise = ADDCOLUMNS(CumulIncidents,"@NouveauProcessus",IF([@Rang]<=7,[Processus],"Autres"))
VAR IncidentsFiltres = FILTER(IncidentsCategorise,[@NouveauProcessus] IN VALUES('Processus avec autres'[Processus]))
RETURN SUMX(IncidentsFiltres,[@Incidents])

At first glance, the result is satisfactory:

Except that if I try to filter the visuals by date, the table no longer displays the first 7 processes + "Others", but all processes without the grouping:

In addition, if I display the table of incidents by process and their classification, this is what I get:

There is certainly something I must have missed... Could someone help me solve these two problems please?

Thanks in advance!

[Ashish_Mathur]

Hi,

I have solved a similar question in the attached file.

Hope this helps.

[noviceBI]

Hello @girishthimmaiah,

Thanks a lot for your answer. Unfortunately it seems that I always miss something since I arrive at the following result (without applying a filter on the dates):

However, I made the following changes:

1/ Rang_Processus_dynamique =
VAR Periode_selectionnee = ALLSELECTED('Incidents 2024'[Date déclaration])
RETURN RANKX(CALCULATETABLE(VALUES('Incidents 2024'[Processus]),Periode_selectionnee),[Total_Incidents],,DESC,DENSE)
2/ Top 7 incidents avec autres =
VAR CumulIncidents = SUMMARIZECOLUMNS('Incidents 2024'[Processus],"@Rang",[Rang_Processus_dynamique],"@Incidents",[Total_Incidents])
VAR IncidentsCategorise = ADDCOLUMNS(CumulIncidents,"@NouveauProcessus",IF([@Rang]<=7,[Processus],"Autres"))
VAR IncidentsFiltres = FILTER(IncidentsCategorise,[@NouveauProcessus] IN VALUES('Processus avec autres'[Processus]))
RETURN SUMX(IncidentsFiltres,[@Incidents])

I would be very grateful if you could help me again!

Thank you!

[MFelix]

Hi @noviceBI ,

Your problem is that you are trying to do the rank on the Incidents, and since there is no context related to the date when yoiu do that filter it will overwrite the context and you get the incorrect result for this you need to do your context for the rank based on the aggregation of values and also of the months so that you can get the correct calculation.

Check this post that has an explanation for when you have several columns:

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

[noviceBI]

Hi @MFelix !

I will consult this article to try to understand, thank you!

But how do you explain the last table with an incorrect ranking and number of incidents, knowing that here I have not filtered by date?

[MFelix]

The questions is that you are calculating the Total incidents for all the table and the result for you measure is always 230 meaning that all of them are on the same rank number.

WIthout any further knowledge of your model it's difficult to pin point the problem but I believe that your Total Incidents measure may be part of the problem.

[noviceBI]

Hello @MFelix,

Yes I suspect that in the absence of details on the model used, it is difficult to understand where the problem comes from.

Here is the link to the pbix file in question, hoping that it helps to find the solution:

https://drive.google.com/file/d/1twjK2NH4O0za03n_1FdqgwPZZsn5PnaA/view?usp=drive_link

Thanks again for your help.

[Anonymous]

Thanks for the reply from MFelix  and girishthimmaiah , please allow me to provide another insight:

Hi, @noviceBI 
Thanks for reaching out to the Microsoft fabric community forum.

Regarding the issue you raised, my solution is as follows:

1.Below are the measure I've created for your needs:

Measure = 
VAR Processus1 = 
    SUMMARIZE(
        TOPN(
            7,
            SUMMARIZE(ALLSELECTED('Incidents 2024'), [Processus], "count", COUNT('Incidents 2024'[Incident])),
            [count], DESC
        ),
        [Processus]
    )
RETURN
    IF(
        SELECTEDVALUE('Processus avec autres'[Processus]) = "Autres",
        CALCULATE(
            COUNT('Incidents 2024'[Incident]),
            NOT('Incidents 2024'[Processus] IN Processus1)
        ),
        CALCULATE(
            COUNT('Incidents 2024'[Incident]),
            'Incidents 2024'[Processus] IN Processus1,
            'Incidents 2024'[Processus] IN VALUES('Processus avec autres'[Processus])
        )
    )

2.Here's my final result, which I hope meets your requirements.

3.Here is a response to a similar issue:

Solved: Struggling with elements required for 'TOP N w/ AL... - Microsoft Fabric Community

Please find the attached pbix relevant to the case.

 
Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
 

Best Regards,

Leroy Lu

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

[noviceBI]

Hello @Anonymous ,

Thank you very much for your message and for your help.
I followed your instructions and I find the same results as you. I am now trying to understand the logic of the "Measure" measure in order to progress, and 3 questions come to mind:
- What does "selectedvalue" refer to in SELECTEDVALUE('Process with others'[Process]) = "Others", since there is no selection strictly speaking in the report?
- Why add the filter 'Incidents 2024'[Process] IN VALUES('Process with others'[Process]) in CALCULATE(
COUNT('Incidents 2024'[Incident]),
'Incidents 2024'[Process] IN Process1,
'Incidents 2024'[Process] IN VALUES('Process with others'[Process])
) ?
- Why does the total displayed in the visuals not take into account the incidents grouped in "Others". Is it possible to count it?
I am aware that my questions may seem silly or basic to expert users like you, but your explanations would be infinitely valuable to me as a beginner!
Thank you again for your help and explanations 😊

[Anonymous · Accepted solution]

Hi, @noviceBI 
 

Thank you very much for your prompt response. I am glad that you are interested in my DAX, which reflects your humble and eager-to-learn attitude.

1.Firstly, regarding the meaning of SELECTEDVALUE. The SELECTEDVALUE function is used to return the value in the current context. If no value is selected, it returns a default value. You can understand it as returning the current row value in a measure. You can replace it with aggregation functions like MAX() or MIN(), and the result will only differ in the total section. This is related to the context dependency of measures.

For the difference between calculated columns and measures, you can refer to the following link:

Solved: What is the difference between a measure and calcu... - Microsoft Fabric Community

2.Secondly, adding the filter 'Incidents 2024'[Processus] IN VALUES('Processus avec autres'[Processus]) is mainly to aggregate the 'Processus avec autres'[Processus] field in our newly created calculated table. If you remove this filter condition, it will return the aggregated value of the 'Incidents 2024'[Processus] IN Processus1 field, which means it will return a fixed value for each row value of the 'Processus avec autres'[Processus] field. As shown in the figure below:

If you add this filter condition, it forms an AND condition. 'Incidents 2024'[Processus] IN Processus1 is to ensure that the returned value is in the TOPN and to aggregate each row value of the 'Processus avec autres'[Processus] field separately. For better understanding, you can also replace the filter 'Incidents 2024'[Processus] IN VALUES('Processus avec autres'[Processus]) with 'Incidents 2024'[Processus] = MAX('Processus avec autres'[Processus]), which is closely related to your first question. Our solution's overall idea is to return the aggregated result that meets the requirements based on the context of the 'Processus avec autres'[Processus] field. Theoretically, its return value should be the content of all 'Processus avec autres'[Processus], but the visualization automatically does not display empty values during aggregation.

3.Finally, regarding your third question, this is also related to the first question. As I mentioned earlier, the total section will be different. You can understand the total section as the output result of a card visual object without the context of the 'Processus avec autres'[Processus] field. Since the output result of SELECTEDVALUE is empty, and empty is not equal to the text "Autres", the output result is false.

The false result is CALCULATE( COUNT('Incidents 2024'[Incident]), 'Incidents 2024'[Processus] IN Processus1, 'Incidents 2024'[Processus] IN VALUES('Processus avec autres'[Processus]) ). You might wonder why it should not return empty here, but the return result includes the total value of all TOPN parts. This is why the VALUES() function is used. If you use 'Incidents 2024'[Processus] = MAX('Processus avec autres'[Processus]), the return result is empty, while VALUES() returns the entire column value in the total section.

For more details, please refer to:

VALUES function (DAX) - DAX | Microsoft Learn

If you need the total value to include all results, you can modify the measure as follows:

Measure = 
VAR Processus1 = 
    SUMMARIZE(
        TOPN(
            [Parameter Value],
            SUMMARIZE(ALLSELECTED('Incidents 2024'), [Processus], "count", COUNT('Incidents 2024'[Incident])),
            [count], DESC
        ),
        [Processus]
    )
RETURN IF(ISINSCOPE('Processus avec autres'[Processus]),
    IF(
        MAX('Processus avec autres'[Processus]) = "Autres",
        CALCULATE(
            COUNT('Incidents 2024'[Incident]),
            NOT('Incidents 2024'[Processus] IN Processus1)
        ),
        CALCULATE(
            COUNT('Incidents 2024'[Incident]),
            'Incidents 2024'[Processus] IN Processus1,
            'Incidents 2024'[Processus] IN VALUES('Processus avec autres'[Processus])
        )
    ),COUNT('Incidents 2024'[Incident]))

Here is the final result:

I hope my answer helps you understand. Of course, this is just my understanding of the practical application of the function.

For a more official explanation, you can refer to the following link:

DAX overview - DAX | Microsoft Learn

I believe you will become an even more outstanding user than us.Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[noviceBI]

Hello @Anonymous ,

Thank you very much for these very detailed explanations and for the time you have spent answering my questions. Unfortunately, the fact that I am not familiar with the terminologies you use to explain how the code works makes it very difficult to understand these explanations.
- When you say in 2. "adding the filter 'Incidents 2024'[Process] IN VALUES('Process with others'[Process]) mainly consists of aggregating the field 'Process with others'[Process] in our newly created calculated table", do you mean that this allows us to have the details of the incidents by Process with others[Process]? What "calculated table" are you talking about?
- Regarding point 1., I have trouble understanding how the MAX could replace SELECTEDVALUE, or even replace IN VALUES in the 2nd filter... I must certainly be missing fundamental notions for me to understand.
- Regarding point 3, I finally managed to understand the parallel with the visual object map without the context of the Process field with others", thank you!
I was also able to test your solution to count the incidents of processes outside the top N in the total, it works perfectly. Thank you again.

I am well aware that my questions may seem heavy to you, but applying solutions without understanding them does not have much interest in my opinion.

Anyway, I mark your answer as a solution, it is really deserved!

[Anonymous]

Hi, @noviceBI 
 

I am glad that my answer could help you, and I appreciate you accepting my answer as the solution. This recognition of my efforts also helps others in the community with similar issues find solutions more quickly.

I believe you need to grasp one core concept: in measures, aggregation functions like MAX(), MIN(), and commonly used SELECTEDVALUE() return the current row's value. This is the main point of measures—they depend on context.

The measure I provided, simply put, checks each row value of 'Processus avec autres'[Processus] to see if it is in our TOPN. If not, it returns the result of COUNT('Incidents 2024'[Incident]) excluding the TOPN part of the 'Incidents 2024' table. If it is, it aggregates the result of COUNT('Incidents 2024'[Incident]) in the 'Incidents 2024' table where 'Incidents 2024'[Processus] meets both the TOPN and equals the current row value of 'Processus avec autres'[Processus].

As for why I mentioned using =MAX() in my previous answer, don't worry too much about it. These parts only show significant differences in the total section, and the difference in regular row values is minimal.

Additionally, the calculated table I mentioned is the 'Processus avec autres' table you created.

My explanation is based on my own understanding and might not be very straightforward. However, I believe that with more practice, your understanding will be deeper than mine.

Best Regards,

Leroy Lu

[noviceBI]

Hello @Anonymous ,

Thank you again for having the patience to answer my last questions. Your explanations are perfectly clear, and I wanted to point out that the more detailed explanations you provided are also very useful for understanding the DAX language, and for further in-depth use.

I will definitely refer to them later.

Thank you very much!

[Anonymous]

Hi, @noviceBI 
I'm glad to hear that my explanation was helpful, and I appreciate you sharing your concerns with us. If you have any questions in the future, please feel free to join the discussion on our forum.

Wishing you a wonderful day!

Best Regards,

Leroy Lu

[girishthimmaiah]

o dynamically recalculate the ranking within the filtered context, follow these steps: 1. Modify the Ranking Measure to Work Dynamically Update your current ranking measure as follows: DAX Rang_Processus_Dynamic = VAR SelectedDateRange = ALLSELECTED('Incidents 2024'[Date]) -- This ensures the ranking respects the date filters RETURN RANKX( CALCULATETABLE( VALUES('Incidents 2024'[Processus]), SelectedDateRange -- Apply the selected date range ), [Total_Incidents], -- Rank based on the count of incidents ,DESC, DENSE ) This adjustment guarantees that the ranking updates dynamically when a date filter is applied. 2. Modify the Measure that Groups "Others" Next, revise your "Top 7 incidents avec autres" measure: DAX Top_7_Incidents_Avec_Autres = VAR CumulIncidents = SUMMARIZECOLUMNS( 'Incidents 2024'[Processus], "@Rang",[Rang_Processus_Dynamic], -- Utilize the new dynamic ranking "@Incidents",[Total_Incidents] ) VAR IncidentsCategorise = ADDCOLUMNS( CumulIncidents, "@NouveauProcessus", IF([@Rang] <= 7, [Processus], "Autres") -- Group lower-ranked incidents into "Others" ) VAR IncidentsFiltres = FILTER( IncidentsCategorise, [@NouveauProcessus] IN VALUES('Processus avec autres'[Processus]) ) RETURN SUMX(IncidentsFiltres, [@Incidents]).