I have the following tables:
My DAX calculation sums up [Days_epi] unique values, from Episodes tbl, grouping them by [ProgramID_epi], [EpisodeID_epi], [ClientID_epi].
So, the SUM of [Days_epi] = 3 + 5 + 31 + 8 + 15 + 20 + 10 = 92
Here is my working code for this:
DaysSUM =
CALCULATE (
SUMX (
SUMMARIZE (
'Episodes',
'Episodes'[EpisodeID_epi],
'Episodes'[ProgramID_epi],
'Episodes'[ClientID_epi],
'Episodes'[Days_epi]
),
'Episodes'[Days_epi]
),
FILTER (
'Episodes',
'Episodes'[Category_epi] = "Homeless"
)
)
I need to add two advanced filters to the calculation above:
Filter 1 should ONLY KEEP records in Episodes, if the records in the Clients have the difference between [DischDate_clnt] and [AdmDate_clnt] >= 365.
Filter 1 in SQL statement is
DATEDIFF(DAY, [AdmDate_clnt], [DischDate_clnt]) >= 365)
After that, Filter 2 should ONLY KEEP records in Episodes, if the records in the Clients have [Date_clnt] >= [AdmDate_clnt] + 12 months. (12 month after the Admission Date)
Filter 2 in SQL statement is
[Date_clnt] <= DATEADD(MONTH, 12, [[AdmDate_clnt])
So, after applying those two filters I expect the records 6 and 10 of the Episodes tbl must be excluded (filtered out), because the records 2 and 3 of the Clients tbl (highlighted in green) are not satisfied my Filter 1 / Filter 2.
Here is the final Episodes dataset I should have (without the 2 records in red):
I was starting to update my DAX code as the following (below). But keep receiving error "Parameter is not the correct type"
enter
DaysSUM_Filters =
CALCULATE (
SUMX (
SUMMARIZE (
'Episodes',
'Episodes'[EpisodeID_epi],
'Episodes'[ProgramID_epi],
'Episodes'[ClientID_epi],
'Episodes'[Days_epi]
),
'Episodes'[Days_epi]
),
FILTER (
'Episodes',
'Episodes'[Category_epi] = "Homeless"
), TREATAS(DATEDIFF('Clients'[AdmDate_clnt],
'Clients'[DischDate_clnt], DAY)>=365,
'Clients'[Date_clnt])
)
Not exactly sure how to set those 2 filters correctly in DAX Power BI, as I am relatively new to it.
Please help!
I can't say about all the case. But what is obvious is that you use TREATAS
in a wrong way. It works like this TREATAS({"Red", "White", "Blue"}, 'Product'[Color])
.
In your case
DATEDIFF('Clients'[AdmDate_clnt],
'Clients'[DischDate_clnt], DAY)>=365
will return TRUE
or FALSE
value. The first argument of TREATAS - is a column or set of columns not a single value.
You can use the filter like this:
FILTER(
'Clients'
,DATEDIFF(
'Clients'[AdmDate_clnt]
,'Clients'[DischDate_clnt]
,DAY
)>=365
)
This will return you a filtered table. This may work if your tables are linked.