countrows if fact table contains specific value

[Anonymous]

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

[AlB · Accepted solution]

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.

[Anonymous]

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.