Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Dom87326
Helper II
Helper II

Conditional expression

Hello, 

 

I'm trying to write a complex conditional expression using the IF or SIWTCH. 

 

My data model is the DimDate:

Dom87326_0-1653399426940.png

 

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!
13 REPLIES 13
tamerj1
Super User
Super User

Hi @Dom87326 

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

 

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!

HI @Dom87326 
How does your report look like?

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!







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

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

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!

 

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

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

@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

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!

@Dom87326 

Ok then delete the .[Year]

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

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

Dom87326_1-1654594473494.png

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!



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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors