Filter not Working

[newhopepdx]

I have a matrix displaying Current period data next to SPLY. (https://www.dropbox.com/scl/fi/9pagceic1e3zuidfvrcbb/DonorTest.pbix?rlkey=gwa1a2utd48q65ngkndtl8sa8&...).

(https://www.dropbox.com/scl/fi/02q6hm6cl5uz3s5eynu10/demo.xlsx?rlkey=zm36rhesums6dme7c4j2edv74&st=6j...)

When a user clicks on a single customer two support table visuals display the detail transacttions that make up the totals for current and SPLY. The measure-driven matrix data is working correctly. But the SPLY table visual is not filtering the data. E.g., The date slicer is set to 1/1/24 to 1/15/24 but the SPLY table visual is displaying all 2023 data for the selected customer. Why isn't the FILTER being implemented?

Here's the DAX for the calculated table:

SPLY Transactions Table =

VAR __minDate = MIN('Date'[Date])

VAR __maxDate = MAX('Date'[Date])

VAR __startDate = DATE(YEAR(__minDate) - 1, MONTH(__minDate), DAY(__minDate))

VAR __endDate = DATE(YEAR(__maxDate) - 1, MONTH(__maxDate), DAY(__maxDate))

RETURN

FILTER (

ADDCOLUMNS(

'FinancialTrans',

"TransactionDate", RELATED('Date'[Date] ),

"AcntName", RELATED(Accounts[Account])

),

[TransactionDate] >= __startDate &&

[TransactionDate] <= __endDate

)

[johnbasha33]

@newhopepdx 
The issue is likely due to calculated tables being static after creation. Unlike measures or calculated columns, calculated tables do not dynamically update when slicers are applied. Since your SPLY Transactions Table is a calculated table, it is only created once when the data model is refreshed and does not react to slicers.

Instead of using a calculated table, you should use a measure that dynamically filters FinancialTrans based on the slicer selections.

SPLY Amount =
VAR __minDate = MIN('Date'[Date])
VAR __maxDate = MAX('Date'[Date])
VAR __startDate = DATE(YEAR(__minDate) - 1, MONTH(__minDate), DAY(__minDate))
VAR __endDate = DATE(YEAR(__maxDate) - 1, MONTH(__maxDate), DAY(__maxDate))

RETURN
CALCULATE(
SUM('FinancialTrans'[Amount]), -- Change this to the relevant column
'Date'[Date] >= __startDate,
'Date'[Date] <= __endDate
)

Alternative: Use a Virtual Table Instead of a Calculated Table

If you need a table structure, use a virtual table inside a measure:

SPLY Transactions =
VAR __minDate = MIN('Date'[Date])
VAR __maxDate = MAX('Date'[Date])
VAR __startDate = DATE(YEAR(__minDate) - 1, MONTH(__minDate), DAY(__minDate))
VAR __endDate = DATE(YEAR(__maxDate) - 1, MONTH(__maxDate), DAY(__maxDate))

RETURN
FILTER (
'FinancialTrans',
'FinancialTrans'[Date] >= __startDate &&
'FinancialTrans'[Date] <= __endDate
)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[newhopepdx]

John, I am using a measure (like the one you've suggested) in the matrix which correctly filters the data and displays the current amount and the SPLY amount.

What I'm stuck on is how to have a separate table visual that displays only rows from the FinancialTrans table within the range of the __startDate and __endDate matching the slicer values. I've been able to do that by creating a reference copy of the FinancialTrans table and which is related to a second slicer where the user sets the date range to match the main slicer less one year....but that is a clunky workaround. 

[v-achippa]

Hi @newhopepdx,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @johnbasha33 for the prompt response.

Instead of using a reference table and a second slicer, we can create a second Date table and relate it to FinancialTrans for dynamic filtering.:

• Duplicate the existing Date table and rename it for ex., SPLY_Date, do not mark the SPLY_Date as a "Date Table" (only one table should be marked as such). So that the original date table is used for normal filtering and the new one will filter last year’s data.
• Now create a relationship between the new SPLY_Date table to FinancialTrans and set the relationship to inactive.
• Use this DAX measure to activate the relationship dynamically,
  

  SPLY Filter =
  VAR __minDate = MIN('Date'[Date]) --> Start date from the slicer
  VAR __maxDate = MAX('Date'[Date]) --> End date from the slicer
  VAR __startDate = DATE(YEAR(__minDate) - 1, MONTH(__minDate), DAY(__minDate)) 
  VAR __endDate = DATE(YEAR(__maxDate) - 1, MONTH(__maxDate), DAY(__maxDate)) 

  RETURN CALCULATE(COUNTROWS('FinancialTrans'), USERELATIONSHIP('FinancialTrans'[TransactionDate], 'SPLY_Date'[Date]), --> Activate the relationship
  'SPLY_Date'[Date] >= __startDate, --> Filter last year's start date
  'SPLY_Date'[Date] <= __endDate --> Filter last year's end date
  )

• Now add the FinancialTrans table to a Table visual and add the above SPLY Filter measure to the visual. And apply a visual level filter, set SPLY Filter > 0. This ensures only transactions that match the last year's date range are shown.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[newhopepdx]

Thanks Anjan,

I've implemented your suggestions. However, the resulting SPLY Transactions table visual does not display any records no matter the dates sliced or whether or not any customer is selected in the matrix. That is puzzling, as your solution makes perfect sense.

I've uploaded the modified .pbix here: https://www.dropbox.com/scl/fi/9mtvtgqod6scyv3229w3g/DonorTest.pbix?rlkey=z8j91x6nmwkf1lazzmrsq59t1&...

Do you see anything I've messed up??

Thanks,

Steve

[v-achippa · Accepted solution]

Hi @newhopepdx,

Since the previous approaches are not working properly, let's use a measure that dynamically filters the transactions:

SPLY Row Filter = VAR __minDate = MIN('Date'[Date])

VAR __maxDate = MAX('Date'[Date])

VAR __startDate = DATE(YEAR(__minDate) - 1, MONTH(__minDate), DAY(__minDate))

VAR __endDate = DATE(YEAR(__maxDate) - 1, MONTH(__maxDate), DAY(__maxDate))

RETURN

IF(SELECTEDVALUE('FinancialTrans'[TransactionDate]) >= __startDate &&

    SELECTEDVALUE('FinancialTrans'[TransactionDate]) <= __endDate,

    1,

    0

)

Use the FinancialTrans as the table visual’s data source. Add the SPLY Row Filter measure to the table visual and apply a visual-level filter i.e set SPLY Row Filter = 1 , this ensures only transactions in the SPLY range are shown.

This will avoid extra tables or slicers and dynamically filters the data correctly.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa