Distinct count of dimension values in matrix

[PowerToBe]

Hi, I have a matrix that contains products on the rows, (1 or 2) years on the columns and 2 measures.

I'm trying to achieve that measure 2 is only shown if there are 2 years in de matrix. (it is always 1 or 2)

Therefore I'm trying to create a variable that I can use to count the number of years in the matrix.

I've tried COUNTROWS(ALLSELECTED('Table'[Year])), but then I get a count of all years in the dimension, instead of only the years in the matrix.

I've tried CALCULATE(SUMX(VALUES('Table'[Year]),1)) but then it always counts 1 because it uses the context of the year of that particular column in the table.

Can anyone help?

Best regards!

[PowerToBe]

Here is the example pbx. The measure is called "NumberOfYears"; see what happens if you add it to the matrix

Share

[MFelix]

Hi  @PowerToBe ,

The first syntax you used should get the correct result but this may depend on the way you are getting the data filter on the matrix is it based on a filter on the visual, a slicer?

Is the table where the column year is being filtered the same one you use for your calculations?

If possible can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[PowerToBe]

Here is the example pbx. The measure is called "NumberOfYears"; see what happens if you add it to the matrix

Share

[MFelix · Accepted solution]

Hi @PowerToBe ,

The problem with this is the context of the year based on the slice of the product, since you are not directly filtering the year but you are showing the years were there we sales values, you need to calculate this on a different way:

NumberOfYears = COUNTROWS(ALLSELECTED(Sales[YearId]))

This raises a problem that is the fact that even for the year that don't have sales you have values so you can redo it like this:

NumberOfYears = IF(SUM(Sales[Sales]) <> BLANK(), COUNTROWS(ALLSELECTED(Sales[YearId])))

On another way if you have the slicer or a filter on visual level to force the year then you can use your original metric:

So this is a question about the context in your case the year context is not getting directly picked up and you are always getting 6 because you are not forcing the years to be filtered on the Years column itself.

File with both options attached.

[PowerToBe]

Hi Miguel Félix, I'll try to get you a mock up example. But in the mean time, to answer your question: yes, the matrix is filtered by a slicer. And yes the table to filter the matrix is the one used in the dax calculation.

Thanks for your quick response!