I am new to this reporting tool and i am struggling to create a Dynamic Line Chart.
So here is the context. I need to get In-ICU Mortality Rate based on the Date,Type,Loc,Department
Slices -Date and Type comes from Dynamic Scope Table (which i have joined to my Orginal Data)
Dynamic Date Scope =
UNION(
ADDCOLUMNS(
CALENDAR(MIN('The Pulse Data'[ICU_STAY_START_DTTM]), MAX('The Pulse Data'[ICU_STAY_END_DTTM])),
"VisualDate", DATE(YEAR([Date]), MONTH([Date]), 1),
"Type", "Monthly",
"Pulse", 1,
"Calendar Year", "CY " & YEAR([Date]),
"Month Name", FORMAT([Date], "mmmm"),
"Month Number", MONTH([Date]),
"Weekday", FORMAT([Date], "dddd"),
"Weekday number", WEEKDAY([Date]),
"Quarter", "Q" & TRUNC((MONTH([Date]) - 1) / 3) + 1
),
ADDCOLUMNS(
CALENDAR(MIN('The Pulse Data'[ICU_STAY_START_DTTM]), MAX('The Pulse Data'[ICU_STAY_END_DTTM])),
"VisualDate", DATE(YEAR([Date]), 3 * (QUARTER([Date]) - 1) + 1, 1),
"Type", "Quarterly",
"Pulse", 2,
"Calendar Year", "CY " & YEAR([Date]),
"Month Name", FORMAT([Date], "mmmm"),
"Month Number", MONTH([Date]),
"Weekday", FORMAT([Date], "dddd"),
"Weekday number", WEEKDAY([Date]),
"Quarter", "Q" & TRUNC((MONTH([Date]) - 1) / 3) + 1
),
ADDCOLUMNS(
CALENDAR(MIN('The Pulse Data'[ICU_STAY_START_DTTM]), MAX('The Pulse Data'[ICU_STAY_END_DTTM])),
"VisualDate", DATE(YEAR([Date]), 1, 1),
"Type", "Yearly",
"Pulse", 3,
"Calendar Year", "CY " & YEAR([Date]),
"Month Name", FORMAT([Date], "mmmm"),
"Month Number", MONTH([Date]),
"Weekday", FORMAT([Date], "dddd"),
"Weekday number", WEEKDAY([Date]),
"Quarter", "Q" & TRUNC((MONTH([Date]) - 1) / 3) + 1
)
)
In-ICU Mortality Rate Numerator =
VAR SelectedType = SELECTEDVALUE('Dynamic Date Scope'[Type], "Monthly")
VAR StartDate = [StartDate]
VAR EndDate = [EndDate]
RETURN
SWITCH(
SelectedType,
"Monthly", CALCULATE(
COUNTROWS('The Pulse Data'),
FILTER(
'The Pulse Data',
YEAR('The Pulse Data'[ICU_STAY_END_DTTM]) = YEAR(StartDate) &&
MONTH('The Pulse Data'[ICU_STAY_END_DTTM]) = MONTH(StartDate) &&
'The Pulse Data'[ICU_STAY_END_DTTM] >= StartDate &&
'The Pulse Data'[ICU_STAY_END_DTTM] <= EndDate &&
'The Pulse Data'[EXP_IN_ICU_BOOL] = 1
)
),
"Quarterly", CALCULATE(
COUNTROWS('The Pulse Data'),
FILTER(
'The Pulse Data',
YEAR('The Pulse Data'[ICU_STAY_END_DTTM]) = YEAR(StartDate) &&
QUARTER('The Pulse Data'[ICU_STAY_END_DTTM]) = QUARTER(StartDate) &&
'The Pulse Data'[ICU_STAY_END_DTTM] >= StartDate &&
'The Pulse Data'[ICU_STAY_END_DTTM] <= EndDate &&
'The Pulse Data'[EXP_IN_ICU_BOOL] = 1
)
),
"Yearly", CALCULATE(
COUNTROWS('The Pulse Data'),
FILTER(
'The Pulse Data',
YEAR('The Pulse Data'[ICU_STAY_END_DTTM]) = YEAR(StartDate) &&
'The Pulse Data'[ICU_STAY_END_DTTM] >= StartDate &&
'The Pulse Data'[ICU_STAY_END_DTTM] <= EndDate &&
'The Pulse Data'[EXP_IN_ICU_BOOL] = 1
)
)
)
In-ICU Mortality Rate Denominator =
VAR SelectedType = SELECTEDVALUE('Dynamic Date Scope'[Type], "Monthly")
VAR StartDate = [StartDate]
VAR EndDate = [EndDate]
RETURN
SWITCH(
SelectedType,
"Monthly", CALCULATE(
COUNTROWS('The Pulse Data'),
FILTER(
'The Pulse Data',
YEAR('The Pulse Data'[ICU_STAY_END_DTTM]) = YEAR(StartDate) &&
MONTH('The Pulse Data'[ICU_STAY_END_DTTM]) = MONTH(StartDate) &&
'The Pulse Data'[ICU_STAY_END_DTTM] >= StartDate &&
'The Pulse Data'[ICU_STAY_END_DTTM] <= EndDate
)
),
"Quarterly", CALCULATE(
COUNTROWS('The Pulse Data'),
FILTER(
'The Pulse Data',
YEAR('The Pulse Data'[ICU_STAY_END_DTTM]) = YEAR(StartDate) &&
QUARTER('The Pulse Data'[ICU_STAY_END_DTTM]) = QUARTER(StartDate) &&
'The Pulse Data'[ICU_STAY_END_DTTM] >= StartDate &&
'The Pulse Data'[ICU_STAY_END_DTTM] <= EndDate
)
),
"Yearly", CALCULATE(
COUNTROWS('The Pulse Data'),
FILTER(
'The Pulse Data',
YEAR('The Pulse Data'[ICU_STAY_END_DTTM]) = YEAR(StartDate) &&
'The Pulse Data'[ICU_STAY_END_DTTM] >= StartDate &&
'The Pulse Data'[ICU_STAY_END_DTTM] <= EndDate
)
)
)
In-ICU Mortality Rate =
DIVIDE([In-ICU Mortality Rate Numerator], [In-ICU Mortality Rate Denominator], 0)
These are my DAX.
The Issue i have is on selecting Date Range,Type,Loc,Department the data is not show show as expected.
On selecting the Loc the data is completely wrong. I am not sure what i am missing at this point.
