Missed Row

[Maahmohammed]

I have a table having a budget for each account No.. And i have another table including the actual expenses for each account no. in each month. We will find the the acctual expebses table doesn't include the account no. 1103 in March.

So, i'd like to capture the missed account no. through creating a measure

Account No	Budget
1101	10
1102	20
1103	30
1104	40
1105	50

Account No	Date	Actual Expnses
1101	1 Jan 2024	10
1102	1 Jan 2024	20
1103	1 Jan 2024	30
1104	1 Jan 2024	40
1105	1 Jan 2024	50
1101	1 Feb 2024	10
1102	1 Feb 2024	20
1103	1 Feb 2024	30
1104	1 Feb 2024	40
1105	1 Feb 2024	50
1101	1 Mar 2024	10
1102	1 Mar 2024	20
1104	1 Mar 2024	40
1105	1 Mar 2024	50

[Anonymous · Accepted solution]

Thanks for the reply from GilbertQ, please allow me to provide another insight.
Hi @Maahmohammed ,

The test dataset is as follows.

Create a measure to mark the missed accounts no.

Missed Account NO. = 
IF (
    SUM ( 'Actual expenses table'[Actual Expnses] ) = BLANK (),
    MAX ( 'Table'[Account No] ),
    BLANK ()
)

Adding this measure to the table visual resulted in the following.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GilbertQ]

Hi @Maahmohammed 

What are would recommend doing is joining the two tables with a left join so that you can then see the missing account number. The first table that I would use is the budget with a left join to the actual data so that you can then see when it is a missing row which you can then flag. This will be done in power query.