Show Matched or Missing instead of True and False

[craig811]

Hi,

My calulation below is showing values True or False:

Missing Invoices From Sales By GL = CALCULATE( COUNTROWS('Sales Invoices by GL Account'), FILTER( 'Sales Invoices by GL Account', 'Sales Invoices by GL Account'[Invoice Number] = EARLIER('VAT Report - VAT Return'[Invoice Number]) ) ) > 0

I want to show 'Matched' if the invoice is found in both reports and 'Missing' if not found, however it just shows Ture and False in my results. I am not sure how to change the values.

Thank you in advance 

[AlB · Accepted solution]

@craig811

Or this, without the variable:

Missing Invoices From Sales By GL =
IF (
    CALCULATE (
        COUNTROWS ( 'Sales Invoices by GL Account' ),
        FILTER (
            'Sales Invoices by GL Account',
            'Sales Invoices by GL Account'[Invoice Number]
                = EARLIER ( 'VAT Report - VAT Return'[Invoice Number] )
        )
    )
        > 0,
    "Matching",
    "Missing"
)

Code formatted with  

[AlB]

Hi @craig811

Are the TRUE and FALSE that you get now correct already, I mean, would you just want to have ' Matched'  in place of TRUE and 'Missing'  in place of FALSE? 

[craig811]

Thank you for the reply.

Yes, the Ture and False values are correct. However, I just want to rename them 'Matched' for True and Missing' for False, within my above formula.

Thank you 

[AlB]

@craig811

Ok, then try this, which reuses your code:

Missing Invoices From Sales By GL =
VAR _Result =
    CALCULATE (
        COUNTROWS ( 'Sales Invoices by GL Account' ),
        FILTER (
            'Sales Invoices by GL Account',
            'Sales Invoices by GL Account'[Invoice Number]
                = EARLIER ( 'VAT Report - VAT Return'[Invoice Number] )
        )
    )
        > 0
RETURN
    IF ( _Result, "Matched", "Missing" )

[AlB · Accepted solution]

@craig811

Or this, without the variable:

Missing Invoices From Sales By GL =
IF (
    CALCULATE (
        COUNTROWS ( 'Sales Invoices by GL Account' ),
        FILTER (
            'Sales Invoices by GL Account',
            'Sales Invoices by GL Account'[Invoice Number]
                = EARLIER ( 'VAT Report - VAT Return'[Invoice Number] )
        )
    )
        > 0,
    "Matching",
    "Missing"
)

Code formatted with  

[craig811 · Accepted solution]

Thank you, that was the solution I was looking for.