Forum Discussion
Question re matrix visualization filtering
Hello all, I have matrix visualization in the following format (see below) - categories of things with summary of their counts per month (its timeseries data). I only want to see in the matrix categories where counts are more than 1 for this month, but i want to see values of them for the previous months too. How do I do that?
E.g. in the example below i only want to see S3/S1/S2 categories because their counts are more than 1 for february, but i want to see their for the whole data range.
Anonymous
You are getting the error because your fields 'TableName'[TimestampUTC].[Month] and/or 'TableName'[TimestampUTC].[Year] are not type integer (they are type text).
MONTH(TODAY()) returns the current month's number, therefore an integer.
YEAR(TODAY()))) returns the current year's number, therefore an integer.
So you either convert both your fields to type integer (using VALUE) or you convert the above expressions to type text (using FORMAT).
Apologies since I should have made the measure clearer. So:
Counts this month = CALCULATE([your count measure], FILTER(Date table, Date table [Month Number] = MONTH(TODAY()) && Date table [Year Number] = YEAR(TODAY())))
Try:
CountsThisMonth = CALCULATE(COUNTROWS('TableName'), FILTER('TableName', VALUE('TableName'[TimestampUTC].[Month]) = MONTH(TODAY()) && VALUE('TableName'[TimestampUTC].[Year]) = YEAR(TODAY())))
4 Replies
PaulDBrown I see, I get the logic of expression now. I modified it a little bit, and it worked beautifully. Thank you!
CountsThisMonth = CALCULATE(COUNTROWS('TableName'), FILTER('TableName', MONTH('TableName'[TimestampUTC]) = MONTH(TODAY()) && YEAR('TableName'[TimestampUTC]) = YEAR(TODAY())))Anonymous
Try:
Counts this month = CALCULATE([your count measure], FILTER(Date table, Date table [Month] = MONTH(TODAY()) && Date table [Year] = YEAR(TODAY())))
Final Measure = SUMX(Date table, IF(NOT(ISBLANK([Counts this month])), [Your count measure]))
PaulDBrown thank you! i tried it and i got an error, what do i do wrong?
CountsThisMonth = CALCULATE(COUNTROWS('TableName'), FILTER('TableName', 'TableName'[TimestampUTC].[Month] = MONTH(TODAY()) && 'TableName'[TimestampUTC].[Year] = YEAR(TODAY())))
Mdxscript(model) (4,148) Calculation error in measure 'Tablename'[CountsThisMonth]:DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.
Anonymous
You are getting the error because your fields 'TableName'[TimestampUTC].[Month] and/or 'TableName'[TimestampUTC].[Year] are not type integer (they are type text).
MONTH(TODAY()) returns the current month's number, therefore an integer.
YEAR(TODAY()))) returns the current year's number, therefore an integer.
So you either convert both your fields to type integer (using VALUE) or you convert the above expressions to type text (using FORMAT).
Apologies since I should have made the measure clearer. So:
Counts this month = CALCULATE([your count measure], FILTER(Date table, Date table [Month Number] = MONTH(TODAY()) && Date table [Year Number] = YEAR(TODAY())))
Try:
CountsThisMonth = CALCULATE(COUNTROWS('TableName'), FILTER('TableName', VALUE('TableName'[TimestampUTC].[Month]) = MONTH(TODAY()) && VALUE('TableName'[TimestampUTC].[Year]) = YEAR(TODAY())))