How to count rows based on 2 conditions

[rishabjain237]

Hi community,

I want to the count of unique invoices in a invoice columns

Invoice eg:

2023/Q3/141

2023/Q3/143

But there is a catch,

If the invoice numbers are like,

2023/Q3/145/1

2023/Q3/145/2

These should be counted as one as well.

Need some help.

Thanks and regards,

Rishab jain

@parry2k Thanks for helping me with 3 rolling averages formula. 

[rishabjain237 · Accepted solution]

@Greg_Deckler 

Hi,

Thank you for a quick response!

I have tried this but it will not take 2023/Q3/251/1

2023/Q3/251/2 has single count. 

[Data-estDog · Accepted solution]

so both formats exist?

InvoiceNumber = 2023/Q3/145

Is this a sub invoice? 2023/Q3/145/2 with the extra "/2"

If you want the following to return a distinct count of 1.....

2023/Q3/145/1

2023/Q3/145/2

2023/Q3/145/3

2023/Q3/145/4

2023/Q3/145/5

DistinctCount should work. But you need to add new column that trims the InvoiceNumber down to the actual Invoice number (lets call it RootInvoiceNumber) to run the distinct count on. Example:

InvoiceNumber , RootInvoiceNumber
2023/Q3/145/1 , 2023/Q3/145
2023/Q3/145/2 , 2023/Q3/145
2023/Q3/145/3 , 2023/Q3/145
2023/Q3/145/4 , 2023/Q3/145
2023/Q3/145/5 , 2023/Q3/145

[Data-estDog · Accepted solution]

so both formats exist?

InvoiceNumber = 2023/Q3/145

Is this a sub invoice? 2023/Q3/145/2 with the extra "/2"

If you want the following to return a distinct count of 1.....

2023/Q3/145/1

2023/Q3/145/2

2023/Q3/145/3

2023/Q3/145/4

2023/Q3/145/5

DistinctCount should work. But you need to add new column that trims the InvoiceNumber down to the actual Invoice number (lets call it RootInvoiceNumber) to run the distinct count on. Example:

InvoiceNumber , RootInvoiceNumber
2023/Q3/145/1 , 2023/Q3/145
2023/Q3/145/2 , 2023/Q3/145
2023/Q3/145/3 , 2023/Q3/145
2023/Q3/145/4 , 2023/Q3/145
2023/Q3/145/5 , 2023/Q3/145

[rishabjain237]

@Greg_Deckler 

Any dax measure possible without creating a new column???

[Data-estDog · Accepted solution]

DistinctInvoices =

    COUNTROWS(

    SUMMARIZE(

        ADDCOLUMNS(

            'Dim Invoice',

            "RootInvoice", MID('Dim Invoice'[InvoiceID], 1, 11)

        ),

        [RootInvoice]

    )

)

Measure are calculated point in time, so If you are going to use it a lot, Might be better to invest in a new column. YOu can always hide it from the model.

[rishabjain237]

@Data-estDog 

After you suggested to create a new column..... I open power query and used display 0 or 1 if we have certain condition and then used that column to set a condition for split based of delimiter.  Thank you for your help

[rishabjain237 · Accepted solution]

@Greg_Deckler 

Hi,

Thank you for a quick response!

I have tried this but it will not take 2023/Q3/251/1

2023/Q3/251/2 has single count. 

[Greg_Deckler]

@rishabjain237 Seems like DISTINCTCOUNT or COUNTROWS(DISTINCT(SELECTCOLUMNS( ... ) ) ) should work for this.