Forum Discussion

newhopepdx's avatar
newhopepdx
Resolver I
1 year ago
Solved

Filter not Working

I have a matrix displaying Current period data next to SPLY. (https://www.dropbox.com/scl/fi/9pagceic1e3zuidfvrcbb/DonorTest.pbix?rlkey=gwa1a2utd48q65ngkndtl8sa8&st=0qs90bos&dl=0). (https://www.drop...
Need Help
  • v-achippa's avatar
    v-achippa
    1 year ago

    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

v-achippa's avatar
v-achippa
Community Support
1 year ago

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's avatar
newhopepdx
Resolver I
1 year ago

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&st=36zwp09r&dl=0

 

Do you see anything I've messed up??

Thanks,

Steve

  • v-achippa's avatar
    v-achippa
    Community Support
    1 year ago

    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