topic Re: DAX help in DAX Commands and Tips

DAX help

Assadujaman — Fri, 24 Feb 2023 03:43:51 GMT

Data table 1

Department	Total Union and NOn-Union 2020	Total Union and NOn-Union 2021	Number of Union 2020	Number of Union 2021	Number of Non-Union 2020	Number of Non-Union 2021	Number of Leaders in 2020	Number of Leaders in 2021
Sales	42	41	20	22	22	19	5	6
Finance	35	36	15	15	20	21	3	3

Data table 2

EE ID	Termination Reason	Effective date Year	EE Type	Department	Leader/ No Leader
XXX	A	2020	Union	Sales	Leader
ZZZ	B	2021	Non-Union	Sales	Leader
YYY	A	2021	Non-Union	Finance	No Leader

I wanted to create a table like this.

Department	2020	2021
Sales	Total Termination reason if A / (Total Union + non Union)	Total Termination reason if A / (Total Union + non Union)
Finance

Department

2020

2021

Sales

Total Termination of leader / (Count of leader)

SalesTotal Termination of leader / (Count of leader)

Finance

Re: DAX help

lbendlin — Sun, 26 Feb 2023 03:27:58 GMT

That's not really a DAX question. You will want to consider bringing your source table into a format that can be used by Power BI. You can try unpivoting in Power Query but that would have to be done in pairs.

Department

Year

Union

Non-Union

Leaders

Re: DAX help

Anonymous — Fri, 03 Mar 2023 06:19:26 GMT

Hi Assadujaman ,

Here are the steps you can follow：

1. In Power Query – Check the rest of the columns except [Department].

Result：

2. Create calculated table.

True = VAR _Department2020 = MAXX ( FILTER ( ALL ( Table2 ), 'Table2'[Termination Reason] = "A" && 'Table2'[Effective date Year] = 2020 ), 'Table2'[Department] ) VAR _Department2021 = MAXX ( FILTER ( ALL ( Table2 ), 'Table2'[Termination Reason] = "A" && 'Table2'[Effective date Year] = 2021 ), 'Table2'[Department] ) RETURN SUMMARIZE ( 'Table1', 'Table1'[Department], "2020", IF ( [Department] = _Department2020, SUMX ( FILTER ( ALL ( Table1 ), VALUE ( RIGHT ( 'Table1'[Attribute], 4 ) ) = 2020 && 'Table1'[Department] = EARLIER ( [Department] ) && CONTAINSSTRING ( 'Table1'[Attribute], "Total Union and NOn-Union" ) = TRUE () ), [Value] ) ), "2021", IF ( [Department] = _Department2021, SUMX ( FILTER ( ALL ( Table1 ), VALUE ( RIGHT ( 'Table1'[Attribute], 4 ) ) = 2021 && 'Table1'[Department] = EARLIER ( [Department] ) && CONTAINSSTRING ( 'Table1'[Attribute], "Total Union and NOn-Union" ) = TRUE () ), [Value] ) ) )

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly