DAX help

[Assadujaman]

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

[Anonymous · Accepted solution]

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

[lbendlin]

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