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...
tamerj1's avatar
tamerj1
Community Champion
4 years ago

Dom87326 
I think it might be much more simple than we thought. Please try

Check Filtered =
ISINSCOPE ( DimCustomer[CustomerName] )
    && COUNTROWS ( VALUES ( Fact[Date].[Year] ) ) = 1
    && COUNTROWS ( ALL ( Fact[Date].[Year] ) ) = 1
Dom87326's avatar
Dom87326
Helper II
4 years ago

Hello tamerj1 ,

Sorry for the late reply. As I wrote before, I'm tyring to create a white coloured shape to overlay on top of other visuals with conditional fill attribute, that will be transparent based on the filter selections.

 

Measure to return TRUE/FALSE:

Check Filtered =
ISINSCOPE ( DimCustomer[CustomerName] )
&& COUNTROWS ( VALUES ( Fact[Date].[Year] ) ) = 1
&& COUNTROWS ( ALL ( Fact[Date].[Year] ) ) = 1​

 

Make Transparent =
IF(
[Check Filtered],
"White",
"#FFFFFF00" -- returns transparent if [Check Filtered] is False
)

Unfortunately, with you sugested approach I reveice a syntax error for Fact[Date].[Year]. Fact[Date] column is date type, with 2021-01-01 values formatted as YYYY. 

 

Thanks!

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Dom87326 

    Ok then delete the .[Year]

    Check Filtered =
ISINSCOPE ( DimCustomer[CustomerName] )
    && COUNTROWS ( VALUES ( Fact[Date] ) ) = 1
    && COUNTROWS ( ALL ( Fact[Date] ) ) = 1
  • Dom87326's avatar
    Dom87326
    Helper II
    4 years ago

    tamerj1 ,

    I'm not using [Check Filtered] in any visual, rather as an input to [Make Transparent], therefore I guess HASONEVALUE suits better in this case?

    In this example I've selected a single CustomerKey = 094543 in a slicer, and expect [Check Filtered] to return TRUE.

    Fact:

    CustomerKey Date Sales Year

    094543 20210101 54986 2021

    However I'm unable to get TRUE for [Check Filtered]. In the table below I've distingueshed each part of the formula we use. 

    Why do you think below measure is returning FALSE?

     

    Check Filtered =
HASONEVALUE( DimCustomer[CustomerName] )
    && COUNTROWS ( VALUES ( Fact[Date] ) ) = 1
    && COUNTROWS ( ALL ( Fact[Date] ) ) = 1

     


    Thanks a lot!