Forum Discussion
countrows if fact table contains specific value
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...
Hi Anonymous
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.
AlB
Community Champion
Community ChampionHi Anonymous
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.
- AlB
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.