Forum Discussion

Dom87326's avatar
Dom87326
Icon for Helper II rankHelper II
4 years ago

Conditional expression

Hello, 

 

I'm trying to write a complex conditional expression using the IF or SIWTCH. 

 

My data model is the DimDate:

 

DimCustomer table:
CustomerKey CustomerName

094234 A
094112 B
094543 C
094092 D

 

Fact

CustomerKey Date Sales Year

094234 20200101 14512 2020 
094112 20200102 43212 2020 
094543 20210101 54986 2021 
094092 20210102 12321 2021 

 

Data model:

CustomerKey[DimCustomer] 1:* CustomerKey[Fact]

Date[DimDate] 1:* Date[Fact]

I'm trying to use this code:

SWITCH(
TRUE(),
ISFILTERED(DimCustomer) && VALUES(Fact[Date]) = 2021), TRUE(),
ISFILTERED(DimCustomer) && VALUES(Fact[Date]) <> 2021), FALSE(),
TRUE()
)
 
My intended result is that if DimCustomer filter is activated (from filters pane or visual) and that customer has 2021 year records in the fact table, formula would return TRUE/FALSE boolean. 
 
Thanks in advance!

13 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Dom87326 

    If I correctly understand then you beed to use ISINSCOPE instead of ISFILTERED

     

    • Dom87326's avatar
      Dom87326
      Icon for Helper II rankHelper II
      4 years ago

      Hello, 

       

      Thanks, I've used ISINSCOPE for DimCustomer and it returns TRUE if customer filer is activated. However, I can't check the secong arguement in condition. Is there a way to check if there's any rows with 2021 or 2022 in the fact after DimCustomer filter context has been applied? Going with values doesn't work for me, because it seems to override the DimCustomer filter context. 

       

      Thanks!