Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
fabo
Advocate III
Advocate III

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. Visually:

 

original1.PNG

 

 The data model is the following:

 

data_model.PNG

 

What I need to do is a "PU" Top 20 (Plant Top 20) in chart 2 (chart in the right side) only if there is no Region selected in chart 1 (chart in the left side).  If there is at least one Region selected, then chart 2 is simply filtered by that Region showing only the plants located there.

 

original2.PNG

 

What I did

 

I added a calculated column in plant table (T_PU) named Rank:

 

Rank =

RANKX(

 ALL(T_PU);

 CALCULATE([Sum of Production])

)

 

And these measures:

 

Sum of Production = SUM(FT_Production[Production])

 

Production by PU =

IF(

 [No of Selected Regions] <> [No of Total Regions];

//This is True when at least one region is selected

 CALCULATE([Sum of Production]);  CALCULATE([Production Top 20])

)

 

No of Selected Regions = DISTINCTCOUNT(T_Region[id_region])

 

No of Total Regions =

CALCULATE(  DISTINCTCOUNT(T_Region[id_region]);  ALL(T_Region)

)

 

Production Top 20 =

CALCULATE(

 [Sum of Production];

 T_PU[Rank] <= 20

)

 

What I got...

 

Two issues:

 

1. A quasi-Top 20 chart. 

 

result1.PNG

 

The Top 20 chart seems to be ok but.. The order is not quite right:

 

comparison.PNG

 

2. When a Region is selected the resulting chart only shows some plants (in some regions, it shows no plant at all).

 

result2.PNG

 

Maybe some of my steps are not appropriate. Or maybe I need a complete different approach. Any suggestion will be welcome.

 

 

Regards!

 

Fabo

 

1 ACCEPTED SOLUTION
Vvelarde
Community Champion
Community Champion

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

ShowTop20OrSelected.gif

 

Regards

 

Victor

Lima - Peru




Lima - Peru

View solution in original post

18 REPLIES 18
v-huizhn-msft
Employee
Employee

Hi @fabo,

In your solution, the right chart is weird, please examined carefully? Do you mind share your sample data for further analysis?

For your right chart, you only find the rows where the rank less than 20, not all the rows. So when you click a region in left chart, it shows the products(were rank <=20) located in the region.

Best Regards,
Angelia

Thank you for replying, Angelia! Smiley Happy

 

Here is a shared folder with my sample data in OneDrive.  I hope you can find something useful from it.

 

I got your explanation about my right chart.  I will rethink how I can make it work properly, i.e. showing top 20 only when no region is selected and just filtering otherwise.

 

fabo

Vvelarde
Community Champion
Community Champion

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

ShowTop20OrSelected.gif

 

Regards

 

Victor

Lima - Peru




Lima - Peru

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 ( 20ALLSELECTED ( T_PU[Production Unit] ), [Sum of Production], DESC )
        )
            > 0,
        [Sum of Production],
        BLANK ()
    )
)

 

 

@NatashaSchuster

 

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

 




Lima - Peru

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

 

@NatashaSchuster

 

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 .

 

 

 

 

 




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

 

@NatashaSchuster

 

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.

 

 

 




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 commentsMy Comments
1The result of INTERSECT()  is a table with the intersection of Production Unit  and the Top 20 Production Units.This part I understand
2In 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? 
3IF  / 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

@NatashaSchuster

 

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)

 

 




Lima - Peru


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

 

image.png

 


You do a COUNTROWS over this table above and WHAT HAPPENS next is not clear  

Could 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 ? 
 

 

@NatashaSchuster

 

Maybe a video can help me to better explanation

 




Lima - Peru

@NatashaSchuster

 

"

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.

 

 

 

 




Lima - Peru

 

Victor,

 

I did not see the video at first.  Thank you  very much for your detailed explanation. 

@NatashaSchuster

 

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

 

 

 

 

 

 

 




Lima - Peru

@NatashaSchuster

 

Use this formula involve in a visual not in a New Table

 

The context made the trick.

 

 




Lima - Peru

Hi @Vvelarde.

 

It's exactly what I was trying to do.  And it worked greatly.  I just had to add one decimal place to numbers (20,0 and 0,0) to be able to run it.  But that is a regional issue.

 

Thank you so much for your help!  Smiley Very Happy

 

Best Regards!

 

fabo

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.