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 | 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
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.
3 Replies
- AlB
Community Champion
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
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.