cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Show Matched or Missing instead of True and False

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 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Show Matched or Missing instead of True and False

@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   www.daxformatter.com

View solution in original post

Highlighted
Helper II
Helper II

Re: Show Matched or Missing instead of True and False

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

View solution in original post

5 REPLIES 5
Highlighted
Super User II
Super User II

Re: Show Matched or Missing instead of True and False

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? 

Highlighted
Helper II
Helper II

Re: Show Matched or Missing instead of True and False

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 

Highlighted
Super User II
Super User II

Re: Show Matched or Missing instead of True and False

@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" )
Highlighted
Super User II
Super User II

Re: Show Matched or Missing instead of True and False

@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   www.daxformatter.com

View solution in original post

Highlighted
Helper II
Helper II

Re: Show Matched or Missing instead of True and False

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

View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors