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 ( 'Treatas1'[Values] ))
VAR varInter2 = INTERSECT(ALL('Fact'[Town]),  VALUES ( 'Treatas2'[Values] ))
RETURN
CALCULATE ( sum('Fact'[Amount]),varInter1, varInter2 ) 

 

Output:

I would like to dynamically control which types are used via a slicer, making the INTERSECT dynamic.

The code I attempted does not work as CALCULATE is expecting a column filter.

 

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

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

 

 

I could obviously use a conditional statement before CALCULATE for this example, but the final product will be using up to 10 types with up to 4 combinations which would be an enormous bit of code.

 

3 Replies

  • 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 )

  • Hi Kashinoda ,

    I think the error is caused of the SWITCH function can't return a column or table, but only a value.

    Best Regards,
    Community Support Team _ kalyj