Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
The data model is the following:
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.
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.
The Top 20 chart seems to be ok but.. The order is not quite right:
2. When a Region is selected the resulting chart only shows some plants (in some regions, it shows no plant at all).
Maybe some of my steps are not appropriate. Or maybe I need a complete different approach. Any suggestion will be welcome.
Regards!
Fabo
Solved! Go to Solution.
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
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!
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
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
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 ()
)
)
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
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
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 .
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
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.
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 |
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)
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 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 ?
"
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.
Victor,
I did not see the video at first. Thank you very much for your detailed explanation.
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().
Use this formula involve in a visual not in a New Table
The context made the trick.
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!
Best Regards!
fabo
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |