Creating a table of later date rows using the Selected date in slicer

[Anonymous]

Hi,

I've the below data table for different tools information.

Application Name (Detailed)	Hosting	Licensing	Tool Life	Target Date
ABBYY Fine Reader (vendor)	Cloud	3rd Party	Sunset	02-12-2020
ACM
Adobe Acrobat	On-Prem	3rd Party	Sunset	02-12-2020
Adobe Acrobat	On-Prem	3rd Party	Sunset	02-12-2020
ALMD Build	Cloud	Proprietary	Sunset	02-01-2021
ALMD Build	Cloud	Proprietary	Sunset	02-01-2021
ALMD build	Cloud	Proprietary	Sunset	02-01-2021
ALMD build	Cloud	Proprietary	Sunset	02-01-2021
ALMD Build	Cloud	Proprietary	Sunset	02-01-2021
Antidote
Apache	On-Prem	Free-3rd Party	Strategic
Apache xerces
Australian Case Citator build	Cloud	Proprietary	Sunset	02-02-2021
Australian Case Citator build	Cloud	Proprietary	Sunset	02-02-2021
Australian Case Citator build	Cloud	Proprietary	Sunset	02-02-2021
Australian Case Citator build	Cloud	Proprietary	Sunset	02-02-2021
Australian Case Citator build	Cloud	Proprietary	Sunset	02-02-2021
Australian Case Citator build	Cloud	Proprietary	Sunset	02-02-2021
Australian Digest

I'm trying to filter the rows with Target Date slicer and create another table. I'm looking to see the rows with date later than the selected slicer date. I also need to show the dinstinct count of "Application Name" column also after filter. 

Below is what I'm seeing now which shows all rows except the selected date. Can some one tell me which contect I'm missing.

Thank you.

 

[v-yingjl]

Hi @Anonymous ,

Based on your description, you need to create a date table based on the target date and use it as a slicer:

Date = DISTINCT('Table'[Target Date])

Then you can create a measure like this to filter the source table and distinct count the Application name column:

Distinct =
IF (
    ISFILTERED ( 'Date'[Target Date] ),
    IF (
        SELECTEDVALUE ( 'Table'[Target Date] ) >= SELECTEDVALUE ( 'Date'[Target Date] ),
        DISTINCTCOUNT ( 'Table'[Application Name (Detailed)] )
    )
)

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yingjl ,

Thanks for the help. I've already got this.

My need is to create a table of entries which has target date later than the selected date irrespective of the count. But the Distinct count of the tools to be displayed in a card. Hope this clarifies to help me more.

Thank you.

[v-yingjl]

Hi @Anonymous ,

Create this visual control measure, put it in the table visual filter and set its value as 1:

Visual control = 
IF (
    ISFILTERED ( 'Date'[Target Date] ),
    IF (
        SELECTEDVALUE ( 'Table'[Target Date] ) >= SELECTEDVALUE ( 'Date'[Target Date] ),
        1,
        0
    )
)

Create this measure to count and put it in the card visual as the above picture:

DistinctCount = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Application Name (Detailed)] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Target Date] >= SELECTEDVALUE ( 'Date'[Target Date] )
    )
)

Attached the modified sample file in the below.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello @v-yingjl

When I continue working on my report, the Visual Control measure does not work when I select multiple dates or date range from the slicer. Is there any way to accomplish this..?
Thank you.

[Anonymous]

@v-yingjl 
Perfect, thank you.

One last curious query.

Can we create a table using the [Visual control] measure..? In other words, a replica of the filtered table..?

[v-yingjl · Accepted solution]

Hi @Anonymous ,

You are welcome.

For your last question, did you mean create a filtered calculated table based on the selected slicer?

If so, I'm afraid it is not supported because a calulated table is only related with the source table columns and we can create it adding some columns to it based on some calculations, slicer in the report could not affect the calculated table.

Hopes it could help you to understand.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.