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

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