Count if multiple column conditions are met across multiple rows

[adoster]

Hello,

Trying to create a DAX for the following table using multiple conditions to get a "Billed By" Total count.

Name ID	Name	Code ID	Code Name	Date	Billed By
2000000001	Michael Smith	10001	Text A	7/1/2021	John Doe
2000000001	Michael Smith	10002	Text B	7/1/2021
2000000001	Michael Smith	10003	Text C	7/1/2021
2000000003	Michael Smith	10002	Text B	7/1/2021
2000000002	Daffy Duck	10001	Text A	9/15/2021	John Doe
2000000002	Daffy Duck	10002	Text B	9/30/2021
2000000002	Daffy Duck	10003	Text C	9/15/2021	Robert Yu

If Code Name = “Text A”

AND Text B exists

AND OR Text C exists

AND Name ID & Date = Name ID & Date of “Text A”

AND Billed By is blank

Count Total to Billed By Name

ELSE

Count Text A only

Results Expected

Name	Date	Count
John Doe	7/1/21	3
John Doe	9/15/21	1

[smpa01]

@adoster  your source data must contain the axis and @v-stephen-msft  is right to point out that you need a fill down to create the axis

| Name ID    | Name          | Code ID | Code Name | Date      | Billed By | Axis      |
|------------|---------------|---------|-----------|-----------|-----------|-----------|
| 2000000001 | Michael Smith | 10001   | Text A    | 7/1/2021  | John Doe  | John Doe  |
| 2000000001 | Michael Smith | 10002   | Text B    | 7/1/2021  |           | John Doe  |
| 2000000001 | Michael Smith | 10003   | Text C    | 7/1/2021  |           | John Doe  |
| 2000000003 | Michael Smith | 10002   | Text B    | 7/1/2021  |           | John Doe  |
| 2000000002 | Daffy Duck    | 10001   | Text A    | 9/15/2021 | John Doe  | John Doe  |
| 2000000002 | Daffy Duck    | 10002   | Text B    | 9/30/2021 |           | John Doe  |
| 2000000002 | Daffy Duck    | 10003   | Text C    | 9/15/2021 | Robert Yu | Robert Yu |

Once you have that, you can use the following measure to return the count

Measure =
VAR _base =
    CALCULATETABLE (
        CALCULATETABLE (
            'Table 1',
            'Table 1'[Code Name] IN { "Text A", "Text B", "Text C" }
        ),
        SUMMARIZE (
            FILTER ( 'Table 1', 'Table 1'[Code Name] = "Text A" ),
            'Table 1'[Name ID],
            'Table 1'[Date]
        )
    )
VAR _count1 =
    CALCULATE ( COUNTROWS ( FILTER ( _base, [Billed By] = BLANK () ) ) )
VAR _count2 =
    CALCULATE ( COUNTROWS ( FILTER ( _base, [Code Name] = "Text A" ) ) )
RETURN
    _count1 + _count2

[v-stephen-msft]

Hi @adoster ,

Please allow me to make a bold guess, whether you can use the fill down function for the empty value in the Billed By column?

Then you can create a measure

Count =
VAR _count =
    COUNTX (
        'Table',
        CALCULATE (
            MAX ( 'Table'[Name ID] ),
            FILTER (
                ALLEXCEPT ( 'Table', 'Table'[Date], 'Table'[Billed By] ),
                [Code Name] = "Text A"
            )
        )
    )
RETURN
    IF ( _count > 1, _count - 1, _count )

Best Regards,

Stephen Tao

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

[ryan_mayu]

@adoster 

you want to count billed by name is blank

for the first result, why count is 3? I think should be 2 (the second row and the third row), since the date of forth row is different.

why the second count result is 1? Although the name ID is the same, the date is still different.

[adoster]

Sorry for the confusion, but total count should also include the initial "Text A" row. The subsequence rows are added to the Billed By from this 1st count.

That's why 3 for first result and 1 for second result.