Forum Discussion

Dom87326's avatar
Dom87326
Helper 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 09...
Dom87326's avatar
Dom87326
Helper II
4 years ago

Hello again, 

 

I'm working on this solution and want to unhide the last years visuals with transparent card if Check Filtered Measure returns TRUE. For now I'm using two conditions:

Check Filtered = 
   AND(HASONEVALUE(DimCustomer[CustomerName]), HASONEVALUE(Fact[Date]))-- Returns True when user selects Customer and Year in filter's pane

However I'd rather want the user to select one filter on DimCustomer and then to check if there's a Date value of 2021 in the Fact for specific client in DAX. As I mentioned before CustomerKey[DimCustomer] 1:* CustomerKey[Fact].

 

Thanks!







tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi Dom87326 
You better use a date table. However, you may try

Check Filtered =
AND ( ISINSCOPE ( DimCustomer[CustomerName] ), ISINSCOPE ( Fact[Date].[Year] ) )
  • Dom87326's avatar
    Dom87326
    Helper II
    4 years ago

    Thanks tamerj1 ,

    I forgot to mention that same CustomerKey might have Dates in the Fact for 2021 and 2020. Is there an alternative for your formula, i.e. 

    Check Filtered =
AND ( ISINSCOPE ( DimCustomer[CustomerName] ), ISINSCOPE ( Fact[Date].[Year] = 2021 ) )

    Regards!

     

    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

      I'm lost again. Please provide a sample of the expected results.

      • Dom87326's avatar
        Dom87326
        Helper II
        4 years ago

        tamerj1 ,

        For the Fact table and when user filter's Customer Key 094543: 

        CustomerKey Date Sales Year

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

         

        I expect that Check Filtered measure returns True (as the Year is 2021). If user filters Customer Key 094092 it returns false, because of Year value 2020. For Customer Key 094234 measure returns false, because it has two year records 2021 and 2020.

         

        Thanks!