Forum Discussion

SteffenSchulze's avatar
SteffenSchulze
New Member
4 years ago
Solved

DAX: Counting multiple values in different columsn

Hi,

I have a table with disinct list of entities. Each entitiy is represented by two more columns which each describe a year.

This is what the table basically looks like:

Entity          Year1          Year2

1                 2020           2020

2                 2021           2022

3                 2021           2021

...                 ...                 ...

 

I would now like to count how often a year apperas in the column "Year1" and how often a year appears in column "Year2" and display this in a new table that could look like this:

 

Year           Amount in "Year1"         Amount in "Year2"

2020              1                                          1

2021              2                                          1

2022              0                                          1

 

Really appreciate your help!

Thanks!

  • VahidDM's avatar
    VahidDM
    4 years ago

    Hi SteffenSchulze 

     

    Try this code to add a new table with DAX:

    Table 2 =
VAR _A =
    VALUES ( 'Table'[Year1] )
VAR _B =
    EXCEPT ( VALUES ( 'Table'[Year2] ), _A )
VAR _C =
    SELECTCOLUMNS ( UNION ( _A, _B ), "Year", [Year1] )
RETURN
    ADDCOLUMNS (
        _C,
        "Year1",
            CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER ( 'Table', 'Table'[Year1] = EARLIER ( [Year] ) )
            ) + 0,
        "Year2",
            CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER ( 'Table', 'Table'[Year2] = EARLIER ( [Year] ) )
            ) + 0
    )

     

    Output:

     

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

4 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    Hi SteffenSchulze 

     

    Try this code to add a new table with DAX:

    Table 2 =
VAR _A =
    VALUES ( 'Table'[Year1] )
VAR _B =
    EXCEPT ( VALUES ( 'Table'[Year2] ), _A )
VAR _C =
    SELECTCOLUMNS ( UNION ( _A, _B ), "Year", [Year1] )
RETURN
    ADDCOLUMNS (
        _C,
        "Year1",
            CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER ( 'Table', 'Table'[Year1] = EARLIER ( [Year] ) )
            ) + 0,
        "Year2",
            CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER ( 'Table', 'Table'[Year2] = EARLIER ( [Year] ) )
            ) + 0
    )

     

    Output:

     

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    SteffenSchulze  I would now like to count how often a year apperas in the column - do you have a seperate table for year?