Issue Adding Measure to Matrix Rows Field

[InsightSeeker]

Hello,

I am using the measure below in my report. However, when I try to add the Customer Status measure to the matrix table's Rows field, I am unable to do so. Could this be due to the way I have created the measure?

Customer Status =

IF(

    [Lost Client Duration] > 3, "Lost Client",

    IF(

        [Months Difference] > 12, "Existing Client",

        IF(

            [Months Difference] > 0 && [Months Difference] <= 12, "NBTO",

            "Trialing Client"

        )

    )

)

Lost Client Duration =

DATEDIFF(

    [Last Sale Date ALL],

    [Last Date in selected Month],

    MONTH

)

Months Difference =

DATEDIFF(

    [First Start Date ALL],

    [Last Date in selected Month],

    MONTH

)

Thanks

[danextian]

Hi @InsightSeeker 

Measures don't have row context so you cannot use them as dimensions. They are evaluated based on the other fields added to a viz. If you want that be shown in the rows field, a placeholder row must exist in the same field as well. For example, if a row field contains A, B, C but doesn't have D but you want D to appear, D must exist in that row field and then write  measure to return the desired value if the current row is D.

If you could provide a workable sample data (not an image) and your expected result from that with the reasoning behind, we might be able to whip up a working solution for you.

[InsightSeeker]

Hi @danextian - Please find attached the sample file.  Click Here

Also the result is to be displayed as below.

Customer Status	Count of Customers	Sales
Existing
Lost
NBTO

Thank You!

[danextian · Accepted solution]

Hi @InsightSeeker 

Create a disconnected table that holds the value of your expected output of your customer status measure. You can use enter data

Then create this measure which evaluates the  [Customer Status] by customer in a temporary table

Status by Customer = 
VAR _tbl =
    --temporary table of status by by customer and the corresponding sales
    ADDCOLUMNS (
        SUMMARIZE ( Customer, Customer[Customer_Code] ),
        "@Status", [Customer Status],
        "@Sales", [Sales]
    )
VAR _filtered_tbl =
    FILTER ( _tbl, [@Status] IN VALUES ( CustomerStatus[Status] ) )
VAR _sales =
    SUMX ( _filtered_tbl, [@Sales] )
VAR _customer =
    COUNTROWS ( _filtered_tbl )
RETURN
    _customer --use _sales  if you want to re turn the sales by customer status

[Bibiano_Geraldo]

Hi @InsightSeeker ,

Matrix Rows field do not support measures, you need to use columns instead.