Forum Discussion

FrancaZ's avatar
FrancaZ
Regular Visitor
2 years ago
Solved

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 ...
  • DataInsights's avatar
    2 years ago

    FrancaZ,

     

    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: