Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 catego...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    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())))

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.

PaulDBrown's avatar
PaulDBrown
Community Champion
5 years ago

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())))