Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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
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 |
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
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
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
@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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |