Forum Discussion
olimilo
Post Prodigy
Post ProdigyVios are Using FILTER() with data from 2++ tables
So, I'm trying to do this measure using DAX:
Initial (NY) =
CALCULATE(
DISTINCTCOUNT(Vios[InsNo])
, FILTER(Vios, Violations[MG Type] = "New York") && FILTER('Scope', 'Scope'[Scope] = "Full Scope")
)Is this not possible to do in Power BI? Scope and Vios are related by the 1:M Scope field; Scope acts as a lookup table for the other tables that reference it.
If not possible, I used Scope as a Page-level filter. Will the DAX still be affected by the Page-level filter even if I don't add the FILTER() for Scope?
What you want is possible like this:
Initial (NY) = CALCULATE ( DISTINCTCOUNT ( Vios[InsNo] ), FILTER ( Vios, Violations[MG Type] = "New York" ), FILTER ( 'Scope', 'Scope'[Scope] = "Full Scope" ) )separate CALCULATE setfilter arguments are "AND" together.
2 Replies
mattbriceSolution Sage
What you want is possible like this:
Initial (NY) = CALCULATE ( DISTINCTCOUNT ( Vios[InsNo] ), FILTER ( Vios, Violations[MG Type] = "New York" ), FILTER ( 'Scope', 'Scope'[Scope] = "Full Scope" ) )separate CALCULATE setfilter arguments are "AND" together.
olimiloThank you! I'll check if I got the correct numbers and get back to this