Forum Discussion

vrad_baker's avatar
vrad_baker
New Member
3 years ago

IF Statement Not Respecting Date Relationship Filter

I have run into a problem I cannot figure out and cannot seem to find an answer on google.

 

I have two tables

1) A date table

This just has dates. It is a dimension table. For reproducing, you can create it with a single field "date" and enter the following data

"1/1/2019"

"3/23/2023" (or within the last 14 days)

2) A data table

This has the data in it.

Model    Version     Date                   Value

1              v1            '1/1/2019'           1

1              v2            '3/21/2023'          2

2              v1           '3/21/2023'           3

 

Now create a report with a table in it with Model and Version being non-summarized. There should be 3 rows, one for each.

 

Now create a page filter based on the Date Table, Date Column. Set it to filter only to the last 14 days.

 

Now you should see 2 rows only, since 1 is hidden.

 

Next, create a new measure on the data table "Measure = IF ( 2 > 1, TRUE, FALSE )".

 

Add this measure to the table. You will now see 3 rows.

 

Now, if you add the Data[Date] as a filter and set it to relative last 14 days, it filters properly.

 

I'm dealing with this in a much more complex data structure, but am able to reproduce it in this very simple model. I can't figure out why the IF isn't filtered. Any help is much appreciated.