Forum Discussion

Assadujaman's avatar
Assadujaman
Regular Visitor
3 years ago
Solved

DAX help

Data table 1

DepartmentTotal Union and NOn-Union 2020Total Union and NOn-Union 2021Number of Union 2020Number of Union 2021Number of Non-Union 2020Number of Non-Union 2021Number of Leaders in 2020Number of Leaders in 2021
Sales42412022221956
Finance35361515202133

Data table 2

EE IDTermination ReasonEffective date YearEE TypeDepartment Leader/ No Leader    
XXXA2020UnionSalesLeader    
ZZZB2021Non-UnionSalesLeader    
YYYA2021Non-UnionFinanceNo Leader    

 

 

I wanted to create a table like this.

 

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

Total Termination reason if A / (Total Union + non Union)  

 

 
Finance   
    

 

 

Department 2020 2021 
SalesTotal Termination of leader / (Count of leader)  

SalesTotal Termination of leader / (Count of leader)  

 

 
Finance   
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
    lbendlin
    Super User
    3 years ago

    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