Forum Discussion

Helper I

2 years ago

Solved

How to count rows based on 2 conditions

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.

Data-estDog
Resolver II
2 years ago
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
  Helper I
  2 years ago
  Greg_Deckler
  Any dax measure possible without creating a new column???
  - Data-estDog
    Resolver II
    2 years ago
    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.
Greg_Deckler
Community Champion
2 years ago
rishabjain237 Seems like DISTINCTCOUNT or COUNTROWS(DISTINCT(SELECTCOLUMNS( ... ) ) ) should work for this.
rishabjain237
Helper I
2 years ago
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.