Matrix. Wrong results in columns when use description in place of key field

[joaquinel]

Hi,

I have a Matrix that I want to divide by a label. My problem is that only works when I use the key field. When I change de key by its description my Matrix shows me the total result in each column.

Thanks

[joaquinel · Accepted solution]

It was my mistake, I was writing the formula in a wrong way. The problem was the FILTER(ALLEXCEPT). The correct way it's to write de Attribute Table which field you want to use it. 

I was using the Facts Table field. I have changed it ContratosServicio[IdCentro]  by Centros[Id] and now it works.

CALCULATE(COUNT(ContratosServicio[Id]),

filter(

     ALLEXCEPT(ContratosServicio,Centros[Id],ServiciosContratables[IdTipoServicio] ), ContratosServicio[FechaAlta] < MIN(calendario[date]) && ContratosServicio[FechaBaja] = BLANK())

[Anonymous]

Hi @joaquinel ,

How did you set your matrix visual? What's the expected result when you put the decription field to replace key field? Could you please provide some sample data(include key field, description field and values fields etc.) with Text format and your expected result with backend scenario and specific examples? It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Please exclude the sensitive info before you share any info with us. Thank you.

How to upload PBI in Community

Best Regards

[joaquinel]

he facts table is ContratosServicio and is linked with Centros by IdCentroContratacion and Id

When I use the IdCentrodeContratacion the data is correct (see below matrix1) but I need to use de Id from Centros table because I need the Description, but the result is not correct since shows me the whole totals in each column (matrix2 below)

Thanks

[Anonymous]

Hi @joaquinel ,

Please try to set the cross filter direction option of the relationship between the table 'ContratosServicio' and 'Centros' as Both, and check if you can get the correct result.

Set cross filter direction as Both

If the above one is not working, please provide the matrix visual Fields setting and its formula if the value field refer any measure or calculated column. Thank you.

Fields setting

Best Regards

[daXtreme]

How to Get Your Question Answered Quickly - Microsoft Power BI Community