英文:
DAX Measure ignoring DATESBETWEEN filter
问题
我有2个表，一个叫做"Treatments"，每一行都包含一个不同的SessionId，它包含了一个Treatment Date和一个Treatment Classification（Completed或Non Completed）。
这个表看起来是这样的
另外，我有一个叫做"Date"的日历表，它包含一个名为"Date"的列，其中每一行都是过去3年的一个不同日期。它还有一个名为Date30D的列，其中包含了30天前的日期，这是通过以下方式计算的。
Date30D = CALCULATE(MIN('Date'[Date]), DATEADD('Date'[Date], -30, DAY))
所以我正在尝试通过一个DAX度量来计算一个列，该列对于日期表中的每一行，计算过去30天内的"Completed"治疗次数。
这是我的度量
Completed Treatments =
CALCULATE(
COUNTROWS(Treatments),
Treatments[Treatment Status] = "Completed",
DATESBETWEEN(
Treatments[Treatment Date],
'Date'[Date30D],
'Date'[Date]))
问题是，它是按天而不是按月计算已完成的治疗次数，它完全忽略了DATESBETWEEN条件，我不知道为什么。我可以怎么做来修复这个问题？
(这是列的样子，结果应该更高，它只是按天计算)
1: https://i.stack.imgur.com/5goBN.png
2: https://i.stack.imgur.com/Atx4r.png
英文:
I've got 2 tables, one is called "Treatments" and it contains a distinct SessionId on each row, it contains a Treatment Date and a Treatment Classification (Completed or Non Completed)
This table looks something like this
Separately, I've got a calendar table called "Date" that contains a column called "Date" where each row is a distinct date for the past 3 years. and it also has a column called Date30D which contains the date for 30 days previous, this is calculated the following way.
Date30D = CALCULATE(MIN('Date'[Date]), DATEADD('Date'[Date], -30, DAY))
So what I am trying to do is calculate a column through a DAX measure that counts for each row on the date table, the number of "Completed" treatments for the past 30 days.
This is my measure
Completed Treatments =
CALCULATE(
COUNTROWS(Treatments),
Treatments[Treatment Status] = "Completed",
DATESBETWEEN(
Treatments[Treatment Date],
'Date'[Date30D],
'Date'[Date]))
The issue is that, it is calculating the number of completed treatments by day, not by month, it is ignoring the DATESBETWEEN condition completely and I don't know why. What can I do to fix this?
(This is what the column looks like, the results should be way higher, it is just calculating by day)
答案1
得分: 0
我是这样做的：
（为了简单起见，我首先使用一个简单的筛选器来计算已完成治疗的数量）。
已完成治疗数量 =
CALCULATE(
COUNTROWS('TreatmentsFact'),
FILTER('TreatmentsFact',[Classification]="Completed")
)
然后我只需要调整时间跨度...
过去30天已完成 =
CALCULATE([已完成治疗数量],
FILTER('DimDate',
'DimDate'[Date]>=MAX('DimDate'[Date])-30 &&
'DimDate'[Date]<='DimDate'[Date])
)
英文:
I did it like this:
(for simplicity, I calculated the Count of Completed Treatments first... with a simple filter).
Count of Completed Treatments =
CALCULATE(
COUNTROWS('TreatmentsFact'),
FILTER('TreatmentsFact',[Classification]="Completed")
)
Then all I have to do is adjust the time span...
Completed Past 30 Days =
CALCULATE([Count of Completed Treatments],
FILTER('DimDate',
'DimDate'[Date]>=MAX('DimDate'[Date])-30 &&
'DimDate'[Date]<='DimDate'[Date])
)
答案2
得分: 0
这是解决此问题的方法。
完成的治疗 =
CALCULATE(
COUNTROWS(Treatments),
Treatments[Treatment Status] = "已完成",
'Date'[Date] >= EARLIER('Date'[Date30D]) && 'Date'[Date] <= EARLIER('Date'[Date])
)
EARLIER 函数将仅按照您行中的日期进行筛选。
英文:
This is the way to fix this issue.
Completed Treatments =
CALCULATE(
COUNTROWS(Treatments),
Treatments[Treatment Status] = "Completed",
'Date'[Date] >= EARLIER('Date'[Date30D]) && 'Date'[Date] <= EARLIER('Date'[Date]))
the EARLIEST function will filter only by the dates within your row.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论