Forum Discussion

Maahmohammed's avatar
Maahmohammed
Helper I
1 year ago
Solved

Missed Row

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 NoBudget
110110
110220
110330
110440
1105

50

 

Account NoDateActual Expnses
11011 Jan 202410
11021 Jan 202420
11031 Jan 202430
11041 Jan 202440
11051 Jan 202450
11011 Feb 202410
11021 Feb 202420
11031 Feb 202430
11041 Feb 202440
11051 Feb 202450
11011 Mar 202410
11021 Mar 202420
11041 Mar 202440
11051 Mar 202450

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

2 Replies

  • GilbertQ's avatar
    GilbertQ
    Super User
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.