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

Hi Victor, 


It's not very obvious why  you are INTERSECTing Unit Values with TOP 20 units by production. The reason is that TOPN is a table funciton as welll as INTERSECT().   Technically, running this without INTERSECT() should yeild the same result but it DOES not. The code below will return the whole list not the TOP 20 (No INTERSECT() in the code below).

 Could you please be so kind to explain how your code is working, especially the INTESCEPT  of VALUES over TOPN part? 

=
IF (
    HASONEVALUE ( T_Region[Region] ),
    [Sum of Production],
    IF (
        COUNTROWS (
            TOPN ( 20, ALLSELECTED ( T_PU[Production Unit] ), [Sum of Production], DESC )
        )
            > 0,
        [Sum of Production],
        BLANK ()
    )
)

 

 

Vvelarde's avatar
Vvelarde
Community Champion
9 years ago

Anonymous

 

Hi, the dax code work very simple:

 

1. When a Region is selected show the productions unit to belong to The Regions Selected.

 

2. When None Region is selected. (This could be read like "All" the regions is selected)

 

Take The value of the Production Unit (Values) (One by One)

 

And another Table with The Top 20 of All Selected PUnits. (of All the Regions selected)

 

I Use The Intersect to evaluate if The Production Unit is included in the Top 20. When the Rows in the Intersect (Table) is greater than 0 means that this PUnit is in the Top 20 so calculate the Sum of Production. If is 0 then Blank(To don't show in the visual)

 

For every Production Unit repeat this evaluation.

 

 

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 ()
    )
)

 

I hope this will be guide to understand the code.

 

Regards

 

Victor

Lima - Peru

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Victor, 

    I see the logic now. You are comparing one by one the values of all production units with TOP 20 production units.  So, this is an iteration process if you are going one by one.  To my knowledge, neither COUNTROWS nor INTERSECT are the iterators. How is this iteration achieved? 

     

    Tks

     

    • Vvelarde's avatar
      Vvelarde
      Community Champion
      9 years ago

      Anonymous

       

      Is not an iterator.

       

      The evaluation (one by one) is "created/Applied" by the visual. 

       

      Everytime that the visual is charting each Production Unit calculate the Dax Code (measure)

       

      So when is 0 the result is blank don't put in the visual .

       

       

       

       

       

      • 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