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
Victor,
Thanks for the clarifications. However, one thing remains little fuzzy.
Let's drop the HASONEVALUE() part since its functionality here is clear and focus on the inner IF()
You DAX logic basically says : ( if converted to some pseudo-DAX code )
IF(
COUNTROWS ( <Top 20 Logic>) > 0,
[Sum of Production],
BLANK ()
)
How are you getting COUNTROWS ( <Top 20 Logic>) > 0 to march with the [Production Unit]
Could you please be so kind to elaborate a little more on your words here : "When the Rows in the INTERSECT (Table) is greater than 0 this means that this [Production Unit] is in the Top 20"
Thanks
Anonymous
I try to explain without dax.
The logic behind this is:
If the ProductionUnit is in the list of Top 20 Product Units show the Total of Production of this PUnit. Is not in the list don't show (Blank).
How obtain this:
Combining Countrows and Intersect.
The result of Intersect is a table with the Intersection of Production Unit and the Top 20 Production Units. In this scenario 1 Row is in the Top 20 and 0 rows if not.
Finally with the IF --Countrows count the rows in the Intersect Table. If Countrows give me a 0 don't show (blank) everything else show the Production Sum.
So this steps Power BI repeat to every ProductionUnit in the moment that is creating (Drawing) the visual.
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().
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
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.