Solved: CALCULATETABLE with multiple filters

jbwestrock · ‎10-03-2022

Hello,

I am trying to create a new table from a much larger existing table, with only the filtered rows. I have tried a few different versions of CalculateTable and other work arounds mentioned in the threads.

The current version I tried is:

HRISHireTermCopy // new table name =

FILTER (
    HRIS_EMPLOYEE_HIR_TER,
    HRIS_EMPLOYEE_HIR_TER[BUSINESS_UNIT]
        IN SELECTCOLUMNS (
            FILTER (
                HRIS_EMPLOYEE_HIR_TER,
                'HRIS_EMPLOYEE_HIR_TER'[BUSINESS_UNIT] = "B4263"
                    && 'HRIS_EMPLOYEE_HIR_TER'[BUSINESS_UNIT] = "B4266"
                    && 'HRIS_EMPLOYEE_HIR_TER'[BUSINESS_UNIT] = "B4356"
                    && 'HRIS_EMPLOYEE_HIR_TER'[BUSINESS_UNIT] = "B4322"
                    && 'HRIS_EMPLOYEE_HIR_TER'[BUSINESS_UNIT] = "B4262"
                    && 'HRIS_EMPLOYEE_HIR_TER'[BUSINESS_UNIT] = "B4342"
                    && 'HRIS_EMPLOYEE_HIR_TER'[BUSINESS_UNIT] = "B4188"
                    && 'HRIS_EMPLOYEE_HIR_TER'[BUSINESS_UNIT] = "B4264"
                    && 'HRIS_EMPLOYEE_HIR_TER'[BUSINESS_UNIT] = "B4370"
                    && 'HRIS_EMPLOYEE_HIR_TER'[BUSINESS_UNIT] = "B4327"
                    && 'HRIS_EMPLOYEE_HIR_TER'[BUSINESS_UNIT] = "B4325"
            ),
            "Business_Unit", HRIS_EMPLOYEE_HIR_TER[BUSINESS_UNIT]
        )
)

&& I have tried:

HRISHireTermCopy // new table name =
FILTER (
    CALCULATETABLE ( ALL ( 'HRIS_EMPLOYEE_HIR_TER' ) ),
    'HRIS_EMPLOYEE_HIR_TER'[BUSINESS_UNIT] = "B4263"
        && 'HRIS_EMPLOYEE_HIR_TER'[BUSINESS_UNIT] = "B4266"
        && 'HRIS_EMPLOYEE_HIR_TER'[BUSINESS_UNIT] = "B4356"
        && 'HRIS_EMPLOYEE_HIR_TER'[BUSINESS_UNIT] = "B4322"
        && 'HRIS_EMPLOYEE_HIR_TER'[BUSINESS_UNIT] = "B4262"
        && 'HRIS_EMPLOYEE_HIR_TER'[BUSINESS_UNIT] = "B4342"
        && 'HRIS_EMPLOYEE_HIR_TER'[BUSINESS_UNIT] = "B4188"
        && 'HRIS_EMPLOYEE_HIR_TER'[BUSINESS_UNIT] = "B4264"
        && 'HRIS_EMPLOYEE_HIR_TER'[BUSINESS_UNIT] = "B4370"
        && 'HRIS_EMPLOYEE_HIR_TER'[BUSINESS_UNIT] = "B4327"
        && 'HRIS_EMPLOYEE_HIR_TER'[BUSINESS_UNIT] = "B4325"
)

Both give me a blank table, no errors, but a blank table.

The below gives me the populated table, obviously without the filters:

HRISHireTermCopy // new table name =

CALCULATETABLE (
HRIS_EMPLOYEE_HIR_TER,
FILTER ( HRIS_EMPLOYEE_HIR_TER, HRIS_EMPLOYEE_HIR_TER[As of] <> "SH" )
)

Your help is appreciated!

Anonymous · ‎10-03-2022

Hi @jbwestrock ,

Step 1: Under reporting go to modelling and click on create new table

Step 2: Enter this into formula

Unique_Table = 

SUMMARIZECOLUMNS (
    Sheet2[BUSINESS_UNIT],
    Sheet2[EMPLID],
    Sheet2[REG_REGION],
    Sheet2[EMPL_TYPE],
    FILTER (
        Sheet2,
        Sheet2[BUSINESS_UNIT]
            IN {
            "B4263",
            "B4266"
        }
    )
)

Step 3: Unique table that you an amend

BR , if that helps please mark this as a solution

View solution in original post

jbwestrock · ‎10-03-2022

Below is a recreated version, but it is close, minus sensitive data:

EMPLID

BUSINESS_UNIT

DESCR

EMPL_TYPE

EFFDT

A.ACTION_DT

ACTION

ACTION_REASON

DESCR2

REG_REGION

DESCR3

LOCATION

HIRES/TERMS

YEARMO

YEARMOAGO

DATENUMEWEEKAGO

YEARMOWEEKAGO

DNWA

YEARMOINDEX

YEARMOEMPLID

DIMDATESINDEX

DEIMDATESWEEKSTARTDATE

LASTWEEKSTARTDATE

DIMDATESYEARMOYRAGO

BUSINESS UNIT AND DESCRIPTION

VARIANCE

123456

B4263

ABC CO

H

10/3/2022

10/1/2022

HIR

AAA

New Hire

USA

City 1

1x1ag1

Hires

Oct-22

Sep-22

41

10/3/2021

XXXXXXXXXX

1

048291

2

41

10/3/2021

44595

B4263_ABC CO

1

234567

B4266

John Co

H

10/3/2022

10/1/2022

HIR

BBB

New Hire

USA

City 2

1x1ag2

Hires

Nov-22

Oct-22

41

10/3/2021

XXXXXXXXXX

1

049433

2

41

10/3/2021

44595

B4266_John Co

1

345678

B4356

Steve Co

H

10/3/2022

10/1/2022

HIR

CCC

New Hire

USA

City 3

1x1ag3

Hires

Dec-22

Nov-22

41

10/3/2021

XXXXXXXXXX

1

050574

2

41

10/3/2021

44595

B4356_Steve Co

-2

456789

B4322

Converting Co

H

10/3/2022

10/1/2022

HIR

Anonymous · ‎10-03-2022

Hi @jbwestrock ,

Step 1: Under reporting go to modelling and click on create new table

Step 2: Enter this into formula

Unique_Table = 

SUMMARIZECOLUMNS (
    Sheet2[BUSINESS_UNIT],
    Sheet2[EMPLID],
    Sheet2[REG_REGION],
    Sheet2[EMPL_TYPE],
    FILTER (
        Sheet2,
        Sheet2[BUSINESS_UNIT]
            IN {
            "B4263",
            "B4266"
        }
    )
)

Step 3: Unique table that you an amend

BR , if that helps please mark this as a solution

jbwestrock · ‎10-03-2022

That worked in my test model. Putting it in actual model.

Thank you!

jbwestrock · ‎10-03-2022

Thanks for the info - so I just need to list all the column names ?

Anonymous · ‎10-03-2022

Yes, thats right.

If it helps , please mark this as a solution 🙂

BR

jbwestrock · ‎10-03-2022

Sorry for the confusion.

I am trying to create a unique table with only the data for this specific list of business units. I want all columns in the other table, but only for the rows that include data for these business units.

Anonymous · ‎10-03-2022

@jbwestrock ,

In that case are you able to share the sample data set with maybe 10 rows and sensitive data removed?

BR

jbwestrock · ‎10-03-2022

Below is rough data, as much as I could get into the character max.

Thank you

Anonymous · ‎10-03-2022

@jbwestrock ,

Correct me if i midunderstood your question. From my understanding you are trying to build a calculated table with a list of filtered Business Units, if that is the case please refer below:

Create a calculated table with formula:

Cust_Table = 
SUMMARIZECOLUMNS (
    Sheet1[BUSINESS UNIT],
    FILTER (
        Sheet1,
        Sheet1[BUSINESS UNIT]
            IN {
            "B4266",
            "B4263",
            "B4356",
            "B4262",
            "B4342",
            "B4188",
            "B4264",
            "B4370",
            "B4327",
            "B4325"
        }
    )
)

BR