Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
Hello need refresher in coming up with this simple countrow measure.
Table 1 | Table 2 | |||
Site ID | Site ID | Category | ||
A | A | Building | ||
B | A | Passive | ||
C | B | Passive | ||
D | D | Building |
I need to countrows table1 re how many sites that have only passive category in table 2. In the example above, i should get 1 site which Site ID B. appreciate if you can help me on this
Solved! Go to Solution.
Try this measure
Measure_ =
VAR auxT_ =
CALCULATETABLE ( DISTINCT ( Table2[SiteID] ), Table2[Category] = "Passive" )
RETURN
COUNTROWS (
FILTER ( auxT_, CALCULATE ( DISTINCTCOUNT ( Table2[Category] ) ) = 1 )
)
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
Try this measure
Measure_ =
VAR auxT_ =
CALCULATETABLE ( DISTINCT ( Table2[SiteID] ), Table2[Category] = "Passive" )
RETURN
COUNTROWS (
FILTER ( auxT_, CALCULATE ( DISTINCTCOUNT ( Table2[Category] ) ) = 1 )
)
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
Thank you this is great just to confirm my understanding of the code. you first created a variable table just to identify which sites have "passive" category. then you iterate with said variable table using filter, to check which of this sites have 1 distinct category which should be passive.
Exactly. Good summary
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.