cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jbwestrock
Frequent Visitor

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!

 

1 ACCEPTED SOLUTION

Hi @jbwestrock ,

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

Dax_Noob_0-1664850303427.png


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

Dax_Noob_1-1664850370649.png


BR , if that helps please mark this as a solution

View solution in original post

9 REPLIES 9
jbwestrock
Frequent Visitor

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

 

EMPLIDBUSINESS_UNITDESCREMPL_TYPEEFFDTA.ACTION_DTACTIONACTION_REASONDESCR2REG_REGIONDESCR3LOCATIONHIRES/TERMSYEARMOYEARMOAGODATENUMEWEEKAGOYEARMOWEEKAGODNWAYEARMOINDEXYEARMOEMPLIDDIMDATESINDEXDEIMDATESWEEKSTARTDATELASTWEEKSTARTDATEDIMDATESYEARMOYRAGOBUSINESS UNIT AND DESCRIPTIONVARIANCE
123456B4263ABC COH10/3/202210/1/2022HIRAAANew HireUSACity 11x1ag1HiresOct-22Sep-224110/3/2021XXXXXXXXXX104829124110/3/202144595B4263_ABC CO1
234567B4266John CoH10/3/202210/1/2022HIRBBBNew HireUSACity 21x1ag2HiresNov-22Oct-224110/3/2021XXXXXXXXXX104943324110/3/202144595B4266_John Co1
345678B4356Steve CoH10/3/202210/1/2022HIRCCCNew HireUSACity 31x1ag3HiresDec-22Nov-224110/3/2021XXXXXXXXXX105057424110/3/202144595B4356_Steve Co-2
456789B4322Converting CoH10/3/202210/1/2022HIR                   

Hi @jbwestrock ,

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

Dax_Noob_0-1664850303427.png


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

Dax_Noob_1-1664850370649.png


BR , if that helps please mark this as a solution

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

 

Thank you!

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

Yes, thats right.

If it helps , please mark this as a solution 🙂

BR

jbwestrock
Frequent Visitor

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.

@jbwestrock ,

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

BR

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

 

Thank you

Dax_Noob
Resolver V
Resolver V

@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

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.