Translate SQL WHERE clauses to a query

[johnsmith92]

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?

[v-kelly-msft]

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,
Kelly

Did I answer your question? Mark my reply as a solution!

[amitchandak]

@johnsmith92 , check if the current employee measure of HR blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[johnsmith92]

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?

[amitchandak · Accepted solution]

@johnsmith92 , if you do not have an active join between two tables then you do not need a crossfilter. Make both join inactive

[johnsmith92]

I did as you suggested, but now I'm getting some wild results that are not expected.

[v-kelly-msft]

Hi  @johnsmith92 ,

Is your issue solved now?

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[johnsmith92]

Yes, using inactive relations between tables does the trick. 🙂

[v-kelly-msft]

Hi @johnsmith92 ,

Great!

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!