topic countrows if fact table contains specific value in DAX Commands and Tips

countrows if fact table contains specific value

Anonymous — Thu, 09 Jun 2022 13:19:48 GMT

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

Re: countrows if fact table contains specific value

AlB — Thu, 09 Jun 2022 13:39:31 GMT

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.

Re: countrows if fact table contains specific value

Anonymous — Thu, 09 Jun 2022 13:56:57 GMT

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.

Re: countrows if fact table contains specific value

AlB — Thu, 09 Jun 2022 14:20:41 GMT

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.