Conditional expression

[Dom87326]

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!

[tamerj1]

Hi @Dom87326 

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

[Dom87326]

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!

[tamerj1]

HI @Dom87326 
How does your report look like?

[Dom87326]

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]

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

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

[Dom87326]

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]

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

[Dom87326]

@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! 

[tamerj1]

@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]

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]

@Dom87326 

Ok then delete the .[Year]

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

[Dom87326]

@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!

[tamerj1]

@Dom87326 
Now it is more clear. But how would you like to display the results? In a table visual sliced by Customer Key or using a card visual?