Forum Discussion

Richard77's avatar
Richard77
Resolver I
2 years ago

Calculated column in a matrix row

Hello community,

I am seeking help on the following issue. I am working on an overview of missing timesheets and would like to display this in a matrix. The 'rows' contain an Index (Unique per person) and a few other fields (like name and country). The only column is a date from a date table an the value is the status of the timesheet per a date.

Shown as a column, meaning in the row fields of the matrix I'd like to show the number of missing timesheets (so next to the name of a person). For this I created a calculated column: 

Dax missing = CALCULATE(countrows(Combined), Combined[Final] = "Missing")
This calculated column is doing a fine job in a table view showing the number of missing timesheets per person.
However, when I drag this field into the row section of the matrix, it gives me the value 1 and a blank, adding a row to each person instead of showing the same number as in the table.
Here is the table: 

and here is the matrix:

 What causes this and how could I fix it?

 

Cheers,

 
Need Help

3 Replies

  • TheoC's avatar
    TheoC
    Community Champion
    2 years ago

    Hi Richard77 

     

    Try to use the identical DAX as a Measure instead of a Calculated Column:

    Missing Timesheets Count =

     

    CALCULATE (

    COUNTROWS ( 'Combined' ) ,
    'Combined'[Final] = "Missing" 

    )

     

    Let me know if that fixes the issue.

     

    Hope this helps.

     

    Theo

     

  • Richard77's avatar
    Richard77
    Resolver I
    2 years ago

    Dear TheoC,

     

    thank you very much for your reply. Power BI however does not allow me to drag the measure into the row context of a matrix.

     

    The calculated column however only gives back the 1 (as it does a countrow and that row applies) and using the table view it automatically sums per person. Should I include this sum somewhere in my formula?

     

    Cheers,

  • TheoC's avatar
    TheoC
    Community Champion
    2 years ago

    Richard77 my apologies. I misunderstood what you are wanting.

     

    In the matrix visual context, the measure is the value, not the row.  If you are wanting the sum using your calculated column, you could use "ALLEXCEPT" but again it's in the context of each row of the "Combined" table and won't aggregate dynamically.

     

    For your requirement, using a measure is more appropriate given it calculates dynamically based on the current context of the report, which includes filters, slicers, and the layout of the matrix visual.

     

    If the earlier measure doesn't provide the output you're after, you can give this a go to calculate the values based on the Index:

     

    Measure = 

SUMX(
    VALUES ( 'Combined'[Index] ) , 
    CALCULATE (
        COUNTROWS ( 'Combined' ) ,
        'Combined'[Final] = "Missing"
    )
)