topic Re: Conditional expression in DAX Commands and Tips

Conditional expression

Dom87326 — Tue, 24 May 2022 14:01:24 GMT

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!

Re: Conditional expression

tamerj1 — Tue, 24 May 2022 17:47:04 GMT

Hi Dom87326

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

Re: Conditional expression

Dom87326 — Wed, 25 May 2022 10:23:41 GMT

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!

Re: Conditional expression

tamerj1 — Wed, 25 May 2022 10:29:35 GMT

HI Dom87326
How does your report look like?

Re: Conditional expression

Dom87326 — Wed, 25 May 2022 10:53:08 GMT

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!

Re: Conditional expression

tamerj1 — Wed, 25 May 2022 12:47:04 GMT

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

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

Re: Conditional expression

Dom87326 — Wed, 25 May 2022 12:56:13 GMT

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!

Re: Conditional expression

tamerj1 — Wed, 25 May 2022 13:03:33 GMT

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

Re: Conditional expression

Dom87326 — Thu, 26 May 2022 09:26:08 GMT

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!

Re: Conditional expression

tamerj1 — Thu, 26 May 2022 09:31:45 GMT

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?

Re: Conditional expression

tamerj1 — Thu, 26 May 2022 09:45:54 GMT

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

Re: Conditional expression

Dom87326 — Mon, 06 Jun 2022 10:38:16 GMT

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!

Re: Conditional expression

tamerj1 — Mon, 06 Jun 2022 10:41:12 GMT

Dom87326

Ok then delete the .[Year]

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

Re: Conditional expression

Dom87326 — Tue, 07 Jun 2022 09:43:31 GMT

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!