Forum Discussion
Solved
Measure & Calculated filed
Dears,
I have the below table & I'd like to have a measure & a calculated column code to get the count of invoices / Customers / Day
Appreciating your support.
| Invoice No. | Date | Item | Customer ID |
| 1 | 17/11/2024 | A | 100 |
| 1 | 17/11/2024 | B | 100 |
| 1 | 17/11/2024 | C | 100 |
| 2 | 17/11/2024 | A | 100 |
| 2 | 17/11/2024 | B | 100 |
| 2 | 17/11/2024 | C | 100 |
| 3 | 17/11/2024 | A | 200 |
| 3 | 17/11/2024 | B | 200 |
| 3 | 17/11/2024 | C | 200 |
| 4 | 18/11/2024 | A | 100 |
| 4 | 18/11/2024 | B | 100 |
| 4 | 18/11/2024 | C | 100 |
Try this:
CountInvoicesColumn =
CALCULATE(
DISTINCTCOUNT('Table'[Invoice No.]),
ALLEXCEPT('Table', 'Table'[Date], 'Table'[Customer ID])
)Hi Maahmohammed ,
You can create a measure to calculate DISTINCT invoices by this DAX:InvoiceCountMeasure = CALCULATE( DISTINCTCOUNT('YourTable'[Invoice No.]), ALLEXCEPT('YourTable', 'YourTable'[Customer ID], 'YourTable'[Date]) )You result will look like this:
4 Replies
Hi Maahmohammed ,
You can create a measure to calculate DISTINCT invoices by this DAX:InvoiceCountMeasure = CALCULATE( DISTINCTCOUNT('YourTable'[Invoice No.]), ALLEXCEPT('YourTable', 'YourTable'[Customer ID], 'YourTable'[Date]) )You result will look like this:
whats your expected output?
Having the below new column
Invoice No. Date Item Customer ID Count Of invoices / Day / Customer 1 17/11/2024 A 100 2 1 17/11/2024 B 100 2 1 17/11/2024 C 100 2 2 17/11/2024 A 100 2 2 17/11/2024 B 100 2 2 17/11/2024 C 100 2 3 17/11/2024 A 200 1 3 17/11/2024 B 200 1 3 17/11/2024 C 200 1 4 18/11/2024 A 100 1 4 18/11/2024 B 100 1 4 18/11/2024 C 100 1 Try this:
CountInvoicesColumn =
CALCULATE(
DISTINCTCOUNT('Table'[Invoice No.]),
ALLEXCEPT('Table', 'Table'[Date], 'Table'[Customer ID])
)
