topic Re: DAX: Counting multiple values in different columsn in DAX Commands and Tips

DAX: Counting multiple values in different columsn

SteffenSchulze — Thu, 06 Jan 2022 16:13:21 GMT

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!

Re: DAX: Counting multiple values in different columsn

smpa01 — Thu, 06 Jan 2022 16:37:55 GMT

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

Re: DAX: Counting multiple values in different columsn

SteffenSchulze — Thu, 06 Jan 2022 16:59:53 GMT

Yes, I do have a separate table with year.

Re: DAX: Counting multiple values in different columsn

smpa01 — Thu, 06 Jan 2022 17:37:35 GMT

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

Re: DAX: Counting multiple values in different columsn

VahidDM — Thu, 06 Jan 2022 23:30:34 GMT

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/