Filtering date slicer

[SNik]

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

[Eric_Zhang]

---

@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.