Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
craig811
Helper III
Helper III

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

@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

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

View solution in original post

5 REPLIES 5
AlB
Super User
Super User

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? 

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 

@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" )

@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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.