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!

Forum Discussion

Frequent Visitor

3 years ago

Solved

CALCULATETABLE with multiple filters

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!

Need Help

Anonymous

3 years ago

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

9 Replies

Anonymous

Not applicable

3 years ago

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"
        }
    )
)

jbwestrock
Frequent Visitor
3 years ago
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
  Not applicable
  3 years ago
  jbwestrock ,
  
  In that case are you able to share the sample data set with maybe 10 rows and sensitive data removed?
  
  BR
  - jbwestrock
    Frequent Visitor
    3 years ago
    Below is rough data, as much as I could get into the character max.
    
    Thank you

jbwestrock

Frequent Visitor

3 years ago

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

10/3/2022

10/1/2022

HIR

AAA

New Hire

USA

City 1

1x1ag1

Hires

Oct-22

Sep-22

10/3/2021

XXXXXXXXXX

048291

10/3/2021

44595

B4263_ABC CO

234567

B4266

John Co

10/3/2022

10/1/2022

HIR

BBB

New Hire

USA

City 2

1x1ag2

Hires

Nov-22

Oct-22

10/3/2021

XXXXXXXXXX

049433

10/3/2021

44595

B4266_John Co

345678

B4356

Steve Co

10/3/2022

10/1/2022

HIR

CCC

New Hire

USA

City 3

1x1ag3

Hires

Dec-22

Nov-22

10/3/2021

XXXXXXXXXX

050574

10/3/2021

44595

B4356_Steve Co

-2

456789

B4322

Converting Co

10/3/2022

10/1/2022

HIR

Anonymous
Not applicable
3 years ago
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
  Frequent Visitor
  3 years ago
  Thanks for the info - so I just need to list all the column names ?

Forum Discussion

CALCULATETABLE with multiple filters

9 Replies

Related content

CALCULATETABLE with filter from multiple table

FILTER vs CALCULATETABLE

FILTER CALCULATETABLE

calculatetable with conditional filter

CALCULATETABLE with multiple filter