'Filter by Month & Year using slicer was correct for individual records but incorrect after removal of unique id
I have created a date table in Power BI called DimDate with fields, Date, Month (Month Name in Text Format), MonthNum (Month in numeric), MonthSort (Redundant field actually) & Year. This table is used for filtering the records based on the Month & Year.
I intend to retrieve the records based on the filters of Company Name, Month, Year and Status. (You can ignore Company Name and Status as they're working fine)
On the screen, I have a data table looks like this:
InMonthYearRange column acts as the flag to determine whether to filter the records or not.
I have written a Measure with DAX codes as follows. Plan to use InMonthYearRange with value 1 to display the records:
InMonthYearRange =
var _monthNum = SWITCH(SELECTEDVALUE(DimDate[Month])
, "Jan", "01", "Feb", "02", "Mar", "03", "Apr", "04"
, "May", "05", "Jun", "06", "Jul", "07", "Aug", "08"
, "Sep", "09", "Oct", "10", "Nov", "11", "Dec", "12"
, "00" )
var _monthStart =
if(_monthNum=="00", "01", _monthNum)
var _monthEnd =
if(_monthNum=="00", "12", _monthNum)
var _yearStart =
if(ISFILTERED(DimDate[Year]), TRIM(SELECTEDVALUE(DimDate[Year])), "1900")
var _yearEnd =
if(ISFILTERED(DimDate[Year]), TRIM(SELECTEDVALUE(DimDate[Year])), "2999")
var _dateStartCriteria = FORMAT(_yearStart & "-" & _monthStart, "YYYY-MM")
var _dateEndCriteria = FORMAT(_yearEnd & "-" & _monthEnd, "YYYY-MM")
return
if(
FORMAT(SELECTEDVALUE('my_test_event_hd'[Start Date Time]), "YYYY-MM")>= _dateStartCriteria
&&
FORMAT(SELECTEDVALUE('my_test_event_hd'[End Date Time]), "YYYY-MM")<= _dateEndCriteria,
1,0)
I have also attempted with the following code. Plan to use InMonthYearRange with values to display the records:
InMonthYearRange =
var _monthNum = SWITCH(SELECTEDVALUE(DimDate[Month])
, "Jan", "01", "Feb", "02", "Mar", "03", "Apr", "04"
, "May", "05", "Jun", "06", "Jul", "07", "Aug", "08"
, "Sep", "09", "Oct", "10", "Nov", "11", "Dec", "12"
, "00" )
var _monthStart =
if(_monthNum=="00", "01", _monthNum)
var _monthEnd =
if(_monthNum=="00", "12", _monthNum)
var _yearStart =
if(ISFILTERED(DimDate[Year]), TRIM(SELECTEDVALUE(DimDate[Year])), "1900")
var _yearEnd =
if(ISFILTERED(DimDate[Year]), TRIM(SELECTEDVALUE(DimDate[Year])), "2999")
var _dateStartCriteria = FORMAT(_yearStart & "-" & _monthStart, "YYYY-MM")
var _dateEndCriteria = FORMAT(_yearEnd & "-" & _monthEnd, "YYYY-MM")
return
COUNTX(
FILTER(
'my_test_event_hd',
FORMAT(SELECTEDVALUE('my_test_event_hd'[Start Date Time]), "YYYY-MM")>= _dateStartCriteria
&&
FORMAT(SELECTEDVALUE('my_test_event_hd'[End Date Time]), "YYYY-MM")<= _dateEndCriteria
) , 'my_test_event_hd'[Unique ID]
)
But whenever I delete Unique ID, Start Date Time & End Date Time, I notice that the value of InMonthYearRange becomes blank (refer to the screenshot below). That means my plan to filter the records based on this column will not work.
Is there any mistake in my DAX codes? What have I done wrong?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|



