Forum Discussion
Helper IIMeasure to display Project IDs based on data in 2 columns.
The below should limit the measure to only show values when there are 3 entries,
Red for 3 months = VAR StartDate = EOMONTH ( TODAY (), -4 ) + 1 VAR EndDate = EOMONTH ( TODAY (), -1 ) VAR Statuses = CALCULATETABLE ( VALUES ( 'Table'[Overall status] ), DATESBETWEEN ( 'Table'[Reporting date], StartDate, EndDate ) ) VAR NumDates = COUNTROWS ( CALCULATETABLE ( VALUES ( 'Table'[Reporting date] ), DATESBETWEEN ( 'Table'[Reporting date], StartDate, EndDate ) ) ) RETURN IF ( COUNTROWS ( Statuses ) = 1 && "Red" IN Statuses && NumDates = 3, 1 )I'm not sure what you mean in your second question. A DAX calculation can use as many columns as it needs to.
That measure is returning a table of values, so it needs to be used where a table is expected rather than a scalar value. If you are trying to exclude older data from your table visual you could create a measure like
Reporting Dates is in last 3 Months = VAR StartDate = EOMONTH ( TODAY (), -4 ) + 1 VAR EndDate = EOMONTH ( TODAY (), -1 ) RETURN IF ( SELECTEDVALUE ( 'Table'[Reporting date] ) IN DATESBETWEEN ( 'Table'[Reporting date], StartDate, EndDate ), 1 )and then use that as a visual level filter to only show where the value is 1.
Hi John,
Just to add to this, restricting data to show for the latest 3 months only for which we were comparing data for.
Found this setting in the visual level filter to dynamically show only the last 3 months of data.So, no need for a separate measure and this actually makes the visual load faster as well.
Just adding it here for others to refer in future.
Thanks !!
Helper IIHi !
Yes it is .[Date], the date hierarchy is working.
I currently tried the Measure in my actual dataset now.
It is working but there is one thing.
So, since the Reporting Date column actually has many months of data, so, bringing the 'Table'[Reporting Date] in the visual shows all the previous data as well. So, basically data prior to the latest 3 months, where different values like Green or Gray maybe present.
I tried to create a new measure which will filter the reporting date to 3 months only using your DATESBETWEEN function usage and then bring that measure in the [Red for 3 Months] measure. Like this:
Reporting Dates in 3 Months =
VAR StartDate =
EOMONTH ( TODAY (), -4 ) + 1
VAR EndDate =
EOMONTH ( TODAY (), -1 )
RETURN
DATESBETWEEN ( 'Table'[Reporting date], StartDate, EndDate ) Then after I replaced this [Reporting Dates in 3 Months] with all the DATESBETWEEN used in [Red in 3 Months], got an error "A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed".
Can you point out where the mistake is happening?
Thanks!
Super UserThat measure is returning a table of values, so it needs to be used where a table is expected rather than a scalar value. If you are trying to exclude older data from your table visual you could create a measure like
Reporting Dates is in last 3 Months =
VAR StartDate =
EOMONTH ( TODAY (), -4 ) + 1
VAR EndDate =
EOMONTH ( TODAY (), -1 )
RETURN
IF (
SELECTEDVALUE ( 'Table'[Reporting date] )
IN DATESBETWEEN ( 'Table'[Reporting date], StartDate, EndDate ),
1
)
and then use that as a visual level filter to only show where the value is 1.
- SDM_1997
Hi John,
Thank you very much for your help !!
A lot of my problems and doubts were cleared.
Happy Weekend ! - SDM_1997
Hi John,
Just to add to this, restricting data to show for the latest 3 months only for which we were comparing data for.
Found this setting in the visual level filter to dynamically show only the last 3 months of data.So, no need for a separate measure and this actually makes the visual load faster as well.
Just adding it here for others to refer in future.
Thanks !!