DAX: Counting multiple values in different columsn

[SteffenSchulze]

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 · Accepted solution]

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]

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

[SteffenSchulze]

Yes, I do have a separate table with year.

[smpa01]

@SteffenSchulze  spare more deatils? Are those two tables related? How?