Forum Discussion

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...

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!

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

Community Champion

4 years ago

Ok then delete the .[Year]

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

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!