topic Re: IF, (AND/OR), ISFILTERED Measure Combination not working as planned when interacting with Dates in DAX Commands and Tips

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

bipowerbix — Sat, 18 Feb 2023 03:34:13 GMT

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/2022	A	25
11/2/2022	B	45
12/9/2022	A	35
12/15/2022	A	10
12/30/2022	B	40
1/5/2023	A	60
1/28/2023	A	5
2/1/2023	B	80
2/5/2023	B	15
2/9/2023	B	70

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

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

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

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.

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

OwenAuger — Sat, 18 Feb 2023 05:53:34 GMT

Hi there bipowerbix ,

It is generally recommended to create a Date dimension table, when applying filters or any logic relating to date columns. The Date column from the Date table should then be used on slicers and any logical tests relating to the Date filter context. In this example, using a separate Date table ensures the date selection on the slicer corresponds exactly to the filtered Date values. Using Test[Date] means that the filtered values are only those actually present in the Test table.
As far as I can see, the ISFILTERED condition is not required (but I could be missing something - let me know if I am). The Categories slicer will interact with any other visuals on the page as long as interactions are enabled (as they are by default).The expression ISFILTERED ( Test[Categories] ) will return TRUE only if a filter has been placed on the Categories column. This will be true if at least one selection is made on the Categories slicer, but not if no selections have been made (as in the slicer's default state).

So I think you can get the correct behaviour by:

Adding a 'Date' table and creating a 1:many relationship between 'Date'[Date] and Test[Date].
'Date' should contain a contiguous series of dates covering at least the range of dates in Test[Date], and should be marked as a date table (see here).
Use 'Date'[Date] on the slicer.
Rewrite the Formula measure as below, referencing 'Date'[Date] and without the ISFILTERED test:

Formula = VAR new = SUM ( Test[Value] ) * 10 VAR startyear = DATE ( YEAR ( TODAY () ), 1, 1 ) VAR firstD = CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date'[Date] ) ) RETURN IF ( firstD >= startyear, new, 0 -- Returning 0 is fine for use in a card or other visual displaying a single value, -- but recommended in visuals that group by one or more columns. )

Sample PBIX is attached.

Does this work for you?

Regards

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

bipowerbix — Sat, 18 Feb 2023 20:16:09 GMT

Yes, That works, fixed it on my original file, what was happening was, the firstD part was not selecting the right date, and the numbers were being thrown off, I thought maybe the ISFILTERED() function might fix it.

VAR firstD = CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date'[Date] ) )