Forum Discussion

Kashinoda's avatar
Kashinoda
Advocate I
4 years ago

Conditional statements within a CALCULATE filter

Hello, I have a simple measure which intersects my fact table with several disconnected tables and works without issue:   Intersect = VAR varInter1 = INTERSECT(ALL('Fact'[County]), VALUES ( 'Tr...
Need Help
Tips and Tricks
amitchandak's avatar
amitchandak
Super User
4 years ago

Kashinoda , Try if this can work

 

Intersect =
VAR varSelect1 = IF (HASONEVALUE(Treatas1[Type]), VALUES(Treatas1[Type]))
VAR varInter1 = SWITCH ( TRUE (), varSelect1 = "County", filter('Fact', 'Fact'[County] in INTERSECT(ALL('Fact'[County]), VALUES ( 'Treatas1'[Values] ))),
varSelect1 = "Town", filter('Fact', 'Fact'[Town] in INTERSECT(ALL('Fact'[Town]), VALUES ( 'Treatas1'[Values] ))),
varSelect1 = "Road", filter('Fact', 'Fact'[Road] in INTERSECT(ALL('Fact'[Road]), VALUES ( 'Treatas1'[Values] ))))

VAR varSelect2 = IF (HASONEVALUE(Treatas2[Type]), VALUES(Treatas2[Type]))
VAR varInter2 = SWITCH ( TRUE (), varSelect2 = "County", filter('Fact', 'Fact'[County] in INTERSECT(ALL('Fact'[County]), VALUES ( 'Treatas2'[Values] ))),
varSelect2 = "Town", filter('Fact', 'Fact'[Town] in IINTERSECT(ALL('Fact'[Town]), VALUES ( 'Treatas2'[Values] ))),
varSelect2 = "Road", filter('Fact', 'Fact'[Road] in INTERSECT(ALL('Fact'[Road]), VALUES ( 'Treatas2'[Values] ))))
RETURN
CALCULATE ( sum('Fact'[Amount]), varInter1, varInter2 )