Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
|
|
|
@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
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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |