Forum Discussion
Translate SQL WHERE clauses to a query
Hi all! I have a very simple model with 2 tables - one fact with cases and history and one date dimension in SSAS. I need a measure to count open cases at the start of each contextualized period. So for example:
| Date | Open cases start of day |
| 01-01-2021 | 13400 |
| 02-02-2021 | 13230 |
| 03-02-2021 | 13300 |
Now, I can achieve this PER day in MSSQL with the following query:
DECLARE @FilterDate AS DATE;
SET @FilterDate = '2021-01-01'
Select COUNT(*)
FROM FactTable
WHERE TaskCompleted IS NULL
AND IsDeleted = 0
AND DW_ValidFrom < @FilterDate
AND DW_ValidTo >= @FilterDate
In SQL I can create a function that reiterates all of the dates needed for my date variable so I would get the table above. In my tabular model I have a 1:* relationship from DimDate to FactCases on DateKey to DW_ValidFrom
Could someone help me translate this seemingly easy query to a DAX statement?
johnsmith92 , if you do not have an active join between two tables then you do not need a crossfilter. Make both join inactive
8 Replies
johnsmith92 , check if the current employee measure of HR blog can help
Hi amitchandak. Thanks for your prompt reply! Unfortunately we are running SQL Server 2014, so I cannot use the CROSSFILTER function. Is there another way to write it?
johnsmith92 , if you do not have an active join between two tables then you do not need a crossfilter. Make both join inactive
Hi johnsmith92 ,
Try:
Count= var _date=IF(SELECTEDVALUE('Date'[date])=BLANK(),DATE(2021,1,1),SELECTEDVALUE('Date'[date])) Return CALCULATE(COUNTROWS('FactTable'),FILTER(ALL('FactTable'),'FactTable'[TaskCompleted]=BLANK()&&'FactTable'[IsDeleted]=0&& 'FactTable'[DW_ValidFrom]<_date && 'FactTable'[DW_ValidTo]>=_date))Best Regards,
KellyDid I answer your question? Mark my reply as a solution!
