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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bipowerbix
Helper I
Helper I

IF, (AND/OR), ISFILTERED Measure Combination not working as planned when interacting with Dates

Below is the sample data we will use for this experiment, First Column are dates, the second one shows Categories and the last one shows values.

Date   Categories   Value

10/5/2022A25
11/2/2022B45
12/9/2022A35
12/15/2022A10
12/30/2022B40
1/5/2023A60
1/28/2023A5
2/1/2023B80
2/5/2023B15
2/9/2023B70

 

Data has two slicers Date and Categories. Below is the DAX statement I have used

Formula = 
var new = sum(Test[Value]) * 10
var startyear = DATE(YEAR(TODAY()),1,1) 
var firstD = CALCULATE( MIN( Test[Date]), ALLSELECTED(Test[Date]) )
return if( ISFILTERED(Test[Categories]) && firstD >= startyear, new, 0 )

I am writing a DAX Statement to multiply column 3(values) by 10 only if the date range is in the current year 2023.

The StartYear gives the start of the current year, firstD gives the lowest date from the date slicer.

 

Now when I filter dates to 2023, the total value should be 2300 but it shows as 0, Image below

bipowerbix_1-1676690644756.png

 

However, the DAX works when I select A or B, Image below

bipowerbix_2-1676690717255.png

 

 

I have also tried removing ISFILTERED function from the measure even though it is mandatory for this measure to interact with the slicer, Now the value shows 650 when it is expected to be 0 because the year is 2022 and not 2023

bipowerbix_3-1676690811303.png

 

Expected Function: I want the Measure to interact with both the date and category slicer and show (value) * 10 if the date slicer is greater than 1/1/2023 and 0 when in date slicer starts from any date of 2022. The result should work if the category slicer is selected or not selected.

 

 

 

 

1 ACCEPTED SOLUTION
v-binbinyu-msft
Community Support
Community Support

Hi @bipowerbix ,

Please try below dax formula:

Measure =
VAR cur_year =
    YEAR ( TODAY () )
VAR min_year =
    YEAR ( MINX ( 'Table', [Date] ) )
VAR cur_cate =
    SELECTEDVALUE ( 'Table'[Categories] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), YEAR ( [Date] ) = cur_year )
VAR tmp1 =
    FILTER ( tmp, [Categories] = cur_cate )
VAR _val =
    IF (
        ISBLANK ( cur_cate ),
        SUMX ( tmp, [Value] * 10 ),
        SUMX ( tmp1, [Value] * 10 )
    )
RETURN
    IF ( min_year = cur_year, _val, 0 )

vbinbinyumsft_0-1677032638387.png

vbinbinyumsft_1-1677032662179.png

vbinbinyumsft_2-1677032685650.png

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-binbinyu-msft
Community Support
Community Support

Hi @bipowerbix ,

Please try below dax formula:

Measure =
VAR cur_year =
    YEAR ( TODAY () )
VAR min_year =
    YEAR ( MINX ( 'Table', [Date] ) )
VAR cur_cate =
    SELECTEDVALUE ( 'Table'[Categories] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), YEAR ( [Date] ) = cur_year )
VAR tmp1 =
    FILTER ( tmp, [Categories] = cur_cate )
VAR _val =
    IF (
        ISBLANK ( cur_cate ),
        SUMX ( tmp, [Value] * 10 ),
        SUMX ( tmp1, [Value] * 10 )
    )
RETURN
    IF ( min_year = cur_year, _val, 0 )

vbinbinyumsft_0-1677032638387.png

vbinbinyumsft_1-1677032662179.png

vbinbinyumsft_2-1677032685650.png

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors