Forum Discussion

fabo's avatar
fabo
Advocate III
9 years ago
Solved

Conditional Top 20

Hi everyone.    I have this scenario: 219 plants ("Production Unit" or "PU") are located in 15 geographical regions. They produce only one product in different amounts. Data is collected monthly. V...
Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    fabo

     

    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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

 

Vvelarde's avatar
Vvelarde
Community Champion
9 years ago

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.