Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
13 | |
2 | |
1 | |
1 | |
1 |
User | Count |
---|---|
25 | |
3 | |
2 | |
2 | |
2 |