Why does it seem that only hard coded values work in Filtered table expressions in DAX?

Question

I have a measure [D Cohort Start Date Key] which is selected by a filter and derived from a static table of date key values.

I created a table In PBI:

Dynamic List of IDs at Time T = 
var Pdate = CONVERT([D Cohort Start Date Key], DOUBLE)
--assume [D Cohort Start Date Key] evaluates to 20200331
var FilteredTable = FILTER('CustTable', 'CustTable'[DimBusinessDateKey] = Pdate)
RETURN 
SELECTCOLUMNS(FilteredTable,"CustID",[CustID])

However this returns a table with no values when values are expected. I have hard-coded a datekey value of 20200331 instead of Pdate in the formula and it returns the values expected. Why is this? I want to use the DateKey variable (and perhaps other variables and measures selected by the user via filtres) In the filteredTable Variable to make this dynamic.

Answer 1

The issue is the result of Pdate. In fact, if you calculate in the exact filter and row context as FilteredTable, I am sure you will have your answer.

Therefore, you need to debug the variable Pdate. You have two options:

A) Power BI
Create a standalone measure,

Debug:=CONVERT([D Cohort Start Date Key], DOUBLE)

and evaluate it in the same filter and row context as FilteredTable is giving you problems. This might be a non trivial task, therefore, see option B)

B) DAX Studio If you have not been able to evaluate Debug in the same filter context as FilteredTable, use DAX Studio.

EVALUATE 
SELECTCOLUMNS('CustTable', "FilteredValue", 'CustTable'[DimBusinessDateKey], "Pdate", CONVERT([D Cohort Start Date Key], DOUBLE))

This expression generates the table on which the filter of FilteredTable is applied. At this point it will be clear the value of Pdate and you will have the answer to your problem.