Forum Discussion

jbwestrock's avatar
jbwestrock
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 ...
Need Help
  • Anonymous's avatar
    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

jbwestrock's avatar
jbwestrock
Frequent Visitor
3 years ago

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's avatar
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's avatar
    jbwestrock
    Frequent Visitor
    3 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Yes, thats right.

      If it helps , please mark this as a solution ğŸ™‚

      BR

  • jbwestrock's avatar
    jbwestrock
    Frequent Visitor
    3 years ago

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

     

    Thank you!