Filtering date slicer

SNik — Mon, 24 Apr 2017 03:40:16 GMT

I have 3 tables

tbl1 is "Child Development" and it looks like

Period From Day To Day

Baby (0)	0	364
Toddler (1 - 2)	365	1094
Preschooler (3 - 4)	1095	1824
Main childhood (5 - 9)	1825	3649
Pre-teenager (10-12)	3650	4744
Teenager (13-19)	4745	7299
Twentysomething (20-29)	7300	10949
Thirtysomething (30-39)	10950	14599
Fortysomething (40-49)	14600	18249
Fiftysomething (50-59)	18250	21899
Sixtysomething (60-69)	21900	25549
Seventysomething (70-79)	25550	29199
Eightysomething (80-89)	29200	32849
Ninetysomething (90-99)	32850	36499
Centenarian (100-109)	36500	40149
Supercentenarian (110+)	40150	73000

second table "ChildList" looks like

ChildID ChildName DOB

1	AL	January 1, 1982
2	Ze	February 1, 1982
3	Wa	February 27, 1982
4	Sa	April 15, 1982
5	Ta	April 16, 1982
6	Gh	January 1, 1983
7	Jn	February 1, 1983
8	Rd	February 27, 1983
9	Ik	April 12, 1983
10	Po	June 6, 1983

and the last table is DimDate

how can i use the DimDate in a DateSlicer (Fromdate ToDate) and be able to filter the "ChildList" table? and i want to know by the DOB and the ToDate from the DimDate that what Period range are my selected childeren

so i wil have a

- slicer on the DimDate

- A grid that is filtered on the slider

- in the grid i want to show the "Period" and as i change the date slicer the "period" will change

- and finally a bar change that will shoe me the Count of Periods that i have selected in my date range slicer

not sure if its clear, thanks

Nik

Re: Filtering date slicer

Eric_Zhang — Tue, 25 Apr 2017 04:24:05 GMT

@SNik wrote:

I have 3 tables

tbl1 is "Child Development" and it looks like

Period From Day To Day

Baby (0) 0 364

Toddler (1 - 2) 365 1094

Preschooler (3 - 4) 1095 1824

Main childhood (5 - 9) 1825 3649

Pre-teenager (10-12) 3650 4744

Teenager (13-19) 4745 7299

Twentysomething (20-29) 7300 10949

Thirtysomething (30-39) 10950 14599

Fortysomething (40-49) 14600 18249

Fiftysomething (50-59) 18250 21899

Sixtysomething (60-69) 21900 25549

Seventysomething (70-79) 25550 29199

Eightysomething (80-89) 29200 32849

Ninetysomething (90-99) 32850 36499

Centenarian (100-109) 36500 40149

Supercentenarian (110+) 40150 73000

second table "ChildList" looks like

ChildID ChildName DOB

1 AL January 1, 1982

2 Ze February 1, 1982

3 Wa February 27, 1982

4 Sa April 15, 1982

5 Ta April 16, 1982

6 Gh January 1, 1983

7 Jn February 1, 1983

8 Rd February 27, 1983

9 Ik April 12, 1983

10 Po June 6, 1983

and the last table is DimDate

how can i use the DimDate in a DateSlicer (Fromdate ToDate) and be able to filter the "ChildList" table? and i want to know by the DOB and the ToDate from the DimDate that what Period range are my selected childeren

so i wil have a

- slicer on the DimDate

- A grid that is filtered on the slider

- in the grid i want to show the "Period" and as i change the date slicer the "period" will change

- and finally a bar change that will shoe me the Count of Periods that i have selected in my date range slicer

not sure if its clear, thanks

Nik

@SNik

To show "Period" that will changes according to the date silcer, you can create a measure

period_measure = 
IF (
 ISFILTERED ( dim_date[Date] ) && HASONEVALUE ( dim_date[Date] ),
 CALCULATE (
 VALUES ( 'Child Development'[Period] ),
 FILTER (
 'Child Development',
 DATEDIFF ( MAX ( ChildList[DOB] ), MAX ( dim_date[Date] ), DAY )
 >= 'Child Development'[From Day]
 && DATEDIFF ( MAX ( ChildList[DOB] ), MAX ( dim_date[Date] ), DAY )
 <= 'Child Development'[To Day]
 )
 ),
 CALCULATE (
 VALUES ( 'Child Development'[Period] ),
 FILTER (
 'Child Development',
 DATEDIFF ( MAX ( ChildList[DOB] ), TODAY (), DAY )
 >= 'Child Development'[From Day]
 && DATEDIFF ( MAX ( ChildList[DOB] ), TODAY (), DAY )
 <= 'Child Development'[To Day]
 )
 )
)

and finally a bar change that will shoe me the Count of Periods that i have selected in my date range slice

However, a measure can't be used as the "Legend" field, and a calculated column won't reponse to the visual slicer, so you may not achieve this point.

A workaround I can think of is to create another calculated table that contains the peroid information for each Date in dim_date table and relate the calculated table to dim_date. However I'd concern about the performance.

Check more details in the attached pbix.

topic Re: Filtering date slicer in Developer

Filtering date slicer

Re: Filtering date slicer