Forum Discussion
Problem with visuals displaying the first N values and a dynamic "other" group
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.
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 😊
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.
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
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!
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!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