Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Create filtered table based on selected date

Hello,

 

I've been stuck with this issue for hours and I need help understanding why this solution isn't working. I've seen the same solution being suggested for others with similar problems as me, but it just does. Not. Work.

 

What I want to solve is this: 

I have a table with prices that have a "valid from" date and a "valid to" date column.

Based on a dropdown slicer selecting a single date, I want to filter my table so that it contains only the valid prices for the given date. 

 

My script is the following:

 

PricesValidForSelectedDate =

VAR SelectedDate= SELECTEDVALUE('Calendar'[Date_ID]; DATE(2019;1;1))

RETURN
CALCULATETABLE ( VALUES ('Prices');
FILTER ( 'Prices';
 'Prices'[ValidFromDate] <= SelectedDate
  && 'Prices'[ValidToDate] >= SelectedDate)
            )
)
 
The Calendar table is a filtered copy of my dimDate table, where I have selected 1st day of every month (2004-2019) to make the date selection more useable.
 
What happens is that de SelectedDate variable only uses the default value of 2019.01.01, even though I have selected a specific date with my "single select" dropdown slicer. I've double checked with a measure that the "selectedvalue(Date_ID)"-script only gives one value, but this new table does not seem to get this.

 

Please, please, please help me solve this!

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    The issue is that you have created a calculated table. Tables are static, and are only evaluated when the underlying dataset is refreshed...they do NOT update in response to any cross filtering, slicer selection, etc.

     

    So, your code is doing exactly what you told it to: since there is no filter context for the SELECTEDVALUE(), it returns the default value of 2019/01/01.

     

    You can create a measure like this:

    PricesValidForSelectedDate =
VAR SelectedDate =
    SELECTEDVALUE (
        'Calendar'[Date_ID];
        DATE ( 2019; 1; 1 )
    )
VAR FilteredPrices =
    FILTER (
        ALL (
            Prices[ValidFromDate];
            Prices[ValidToDate]
        );
        'Prices'[ValidFromDate] <= SelectedDate
            && 'Prices'[ValidToDate] >= SelectedDate
    )
VAR Result =
    CALCULATE (
        MIN ( Prices[Price Column] );
        FilteredPrices
    )
VAR Check =
    IF (
        HASONEVALUE ( Prices[<Granular Column>] );
        Result
    )
RETURN
    Check

    SelectedDate is just as you wrote it.

     

    FilteredPrices uses a more optimized table.  You only need to scan the unique combinations for [ValidFrom] and [ValidTo], you don't need to scan every single row in the prices table (which, when expanded, could produce a large table in memory).

     

    Result returns the price when you have a single product in the the filter context.  I'm guessing you're putting Product on the rows of your table?

     

    Check just makes sure that you display a blank in the subtotal or grand total section of your table/matrix.  Make sure that <Granular Column> is whatever you're putting in the table.

     

    Hope this helps,

     

    ~ Chris

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    The issue is that you have created a calculated table. Tables are static, and are only evaluated when the underlying dataset is refreshed...they do NOT update in response to any cross filtering, slicer selection, etc.

     

    So, your code is doing exactly what you told it to: since there is no filter context for the SELECTEDVALUE(), it returns the default value of 2019/01/01.

     

    You can create a measure like this:

    PricesValidForSelectedDate =
VAR SelectedDate =
    SELECTEDVALUE (
        'Calendar'[Date_ID];
        DATE ( 2019; 1; 1 )
    )
VAR FilteredPrices =
    FILTER (
        ALL (
            Prices[ValidFromDate];
            Prices[ValidToDate]
        );
        'Prices'[ValidFromDate] <= SelectedDate
            && 'Prices'[ValidToDate] >= SelectedDate
    )
VAR Result =
    CALCULATE (
        MIN ( Prices[Price Column] );
        FilteredPrices
    )
VAR Check =
    IF (
        HASONEVALUE ( Prices[<Granular Column>] );
        Result
    )
RETURN
    Check

    SelectedDate is just as you wrote it.

     

    FilteredPrices uses a more optimized table.  You only need to scan the unique combinations for [ValidFrom] and [ValidTo], you don't need to scan every single row in the prices table (which, when expanded, could produce a large table in memory).

     

    Result returns the price when you have a single product in the the filter context.  I'm guessing you're putting Product on the rows of your table?

     

    Check just makes sure that you display a blank in the subtotal or grand total section of your table/matrix.  Make sure that <Granular Column> is whatever you're putting in the table.

     

    Hope this helps,

     

    ~ Chris

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

    I'd suggest that you transform your source data into one where every date of a date range appears in an individual row.  We will then create a relationship from this Table to the Calendar Table and get your desired result.  Share some data and show the expected result if you need more help.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thank you for your replies, Anonymous and Ashish_Mathur!.

     

    I didn't know that a calculated table isn't affected by slicers, so now I finally understand the problem.

     

    There are three different price columns I want to display per category, which is why I hoped there would be a way to calulate the whole table dynamically based on the slicer. I created a measure for each price column with the script Anonymous suggested, and this gives the result I wanted. 

     

    Thank you for your help, I'm so relieved!

     

    /Lovinda