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. 

IDCreation WeekClosure Week
12004-012004-01
22004-012004-02
32004-022004-02
42004-032004-03

 

My Goal:

I want a visual with the weeks on the X-axis counting IDs created in that week (one bar) and IDs closed in that week (another bar). Hence, for the above data, output should be like:

| x   |   o |     | 

| xo | xo | xo |

--------------

  01  02   03        (x = created, o = closed)

 

My Challenge:

I can easily get this done if I user Power Query to aggregate before, but I want to to have another visual which is linked and displaying the details, i.e. more or less the original table which should be filtered if I click on one of the bars. This works also fine if I just make that char for created or for closed, but anyone an idea how to get that working with both in the same visual?

Need Help
  • DataInsights's avatar
    DataInsights
    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:

     

     

1 Reply

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    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: