cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adoster
New Member

Count if multiple column conditions are met across multiple rows

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

 

 

 

4 REPLIES 4
smpa01
Super User
Super User

@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

 

 

Capture.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


v-stephen-msft
Community Support
Community Support

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?

1.png

 

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 )

2.png

 

 

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
Super User
Super User

@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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!