Forum Discussion
Conditional Top 20
Hi, Please try with this measure:
ProductionShow = IF ( HASONEVALUE ( T_Region[Region] ), [Sum of Production], IF ( COUNTROWS ( INTERSECT ( VALUES ( T_PU[Production Unit] ), TOPN ( 20, ALLSELECTED ( T_PU[Production Unit] ), [Sum of Production], DESC ) ) ) > 0, [Sum of Production], BLANK () ) )Regards
Victor
Lima - Peru
Hello,
I have broken down your answer into 3 steps. Could you please be so kind to answer my question from the (2) second step. I apologize very much if you find me too persistent.
| Steps: | Your comments | My Comments |
| 1 | The result of INTERSECT() is a table with the intersection of Production Unit and the Top 20 Production Units. | This part I understand |
| 2 | In this scenario 1 Row is in the Top 20 and 0 rows if not. | This is the confusing part. 1 Row from what ? You appear to make this distinction between two things 1 or 0 but what I see is in your DAX is an INTERSECT() function that returns back a table of TOP 20 Units by production. What are these ones and zeros? |
| 3 | IF / COUNTROWS() counts the rows in the Intersect Table. If COUNTROWS() gives me a 0 don't show (blank) everything else show the Production Sum. | This part I understand |
Anonymous
| In this scenario 1 Row is in the Top 20 and 0 rows if not. | This is the confusing part. 1 Row from what ? You appear to make this distinction between two things 1 or 0 but what I see is in your DAX is an INTERSECT() function that returns back a table of TOP 20 Units by production. What are these ones and zeros? |
Lets go to evaluate the result of INTERSECT
Example:
When Match the PUNIT in TOP 20 is one ROW with the Production Unit.
Production Unit
UNIT150
Total of Rows = 1
When Don't Match: (The result is A empty Table)
Production Unit
Total of Rows = 0
In Step 3 with Countrows evaluate the result (Total of Rows)
Anonymous
Use this formula involve in a visual not in a New Table
The context made the trick.
Anonymous
"
For example let's take PU82 (it is in TOP 20) and PU150 this one is not a TOP20 Production Unit. How does the story evolves from here ? "
In the Visual you add all the Production Units in Rows. (Axis)
In values is the measure who evaluates for every Production Unit (one to one) if is in the TOP 20 or not.
Anonymous
Maybe a video can help me to better explanation
Anonymous
The logic is this:
The Production Unit PU82 is in the TOP 20 Production Units?
Yes. INTERSECT bring you a Table with the Row (PU82)
So i use CountRows of INTERSECT Table and the answer is 1 . So the result of the measure is the SUM of Production.
The Production Unit PU10000 is in the TOP 20 Production Units?
No. INTERSECT bring you a Empty Table. The CountRows of INTERSECT Table and the answer is 0 . So the result of the measure is Blank().
This is now getting even more confusing. What INTERSECT does is clear it returns a table of TOP 20 production units.This is the INTERSECT Table values below. We got 20 rows here
You do a COUNTROWS over this table above and WHAT HAPPENS next is not clearCould you please so kind to be more specific
Not really sure what do you mean here... "When Match the PUNIT in TOP 20 is one ROW with the Production Unit." ?
Do you mean if we have a match between TOP 20 Production Unit and a Production Unit in the T_PU Dimension ?
For example let's take PU82 (it is in TOP 20) and PU150 this one is not a TOP20 Production Unit. How does the story evolves from here ?
Victor,
I did not see the video at first. Thank you very much for your detailed explanation.