Forum Discussion
FrancaZ
2 years agoRegular Visitor
Counting two different columns on the same X-axis without prior aggregation
My Data: Table1 with columns "ID", "Creation Week", and "Closure Week", e.g. ID Creation Week Closure Week 1 2004-01 2004-01 2 2004-01 2004-02 3 2004-02 2004-02 4 2004-03 ...
- 2 years ago
This solution requires a Weeks table consisting of each possible week. You can create this in Power Query or DAX. This table has no relationship with the data table. Here's a DAX calculated table (I renamed the resulting column to Week):
Weeks = DISTINCT ( UNION ( DISTINCT ( Table1[Creation Week] ), DISTINCT ( Table1[Closure Week] ) ) )Measures:
Count Creation = CALCULATE ( COUNT ( Table1[ID] ), TREATAS ( VALUES ( Weeks[Week] ), Table1[Creation Week] ) )Count Closure = CALCULATE ( COUNT ( Table1[ID] ), TREATAS ( VALUES ( Weeks[Week] ), Table1[Closure Week] ) )Use Weeks[Week] in a visual:
DataInsights
Super User
2 years ago
This solution requires a Weeks table consisting of each possible week. You can create this in Power Query or DAX. This table has no relationship with the data table. Here's a DAX calculated table (I renamed the resulting column to Week):
Weeks =
DISTINCT (
UNION ( DISTINCT ( Table1[Creation Week] ), DISTINCT ( Table1[Closure Week] ) )
)
Measures:
Count Creation =
CALCULATE (
COUNT ( Table1[ID] ),
TREATAS ( VALUES ( Weeks[Week] ), Table1[Creation Week] )
)Count Closure =
CALCULATE (
COUNT ( Table1[ID] ),
TREATAS ( VALUES ( Weeks[Week] ), Table1[Closure Week] )
)
Use Weeks[Week] in a visual: