Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Anonymous
Not applicable

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                   
Anonymous
Not applicable

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 ?

Anonymous
Not applicable

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.

Anonymous
Not applicable

@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

Anonymous
Not applicable

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.