Passing table as filter argument to CALCULATETABLE

[waeltken]

Dear PBI Experts,

I've been listening / reading to Alberto and Marco and they always say that filter arguments are tables in DAX. I'm probably betraying my inexperience here but I've been working through a problem of multi-column deduplication in one of my source tables in DAX using a filtering table that I want to apply against my full table.

My User/Job Info/Pay transactions table lists unique employee events, but the corresponding employee compensation may not change with every row/record as e. g. an employee location change creates a new unique record but their pay info stays the same (is duplicated). Consider the schema below where the pay-related fields are marked in YELLOW. The full table has about 13,000 rows.

Field	Sample Value
Employee Class Adjusted	Regular
User Id	193
User/Employee ID	193
Event Date	12/17/2016
Event	Termination
Event Reason	TERTVTERM-Vol Termination - Other
Last Modified On	12/19/2016
Employee Status	Terminated
First Name	Firsty
Last Name	SamplePerson
Position ID	193
Professional Title/ Position Title
Job Code	HOPO - Supervisor
Job Title	Senior Director, Medical Services
RecurringPay_Event_Date	7/1/2013
CompInfo_Event Reason	PAYMER-Annual Increase
Pay Component	SAL-Salary
HourlyRate	132.945

So in order to distill the Pay related events down to the unique events for each employee I'm using a DAX table function that features User ID and the pay fields, resulting in a deduplicated table of about 4,400 rows. 

Here's the DAX code for this table. I've tested it in DAX Studio.

VAR uniqueuserpayrecords =

DISTINCT(
    SELECTCOLUMNS( 
        Promotions, 
        "User Id", [User Id],
        "RecurringPay_Event_Date", [RecurringPay_Event_Date],
        "CompInfo_Event Reason", [CompInfo_Event Reason],
        "Pay Component", [Pay Component],
        "HourlyRate", [HourlyRate]
    )
)

The resulting table looks like this. Each combination of the 5 columns is unique in the full table.

User Id	Recurring Pay Event Date	CompInfo_Event Reason	Pay Component	HourlyRate
52	7/1/2018	PAYMER-Annual Increase	SAL-Salary	134.6528
52	8/1/2016	PROPWPHR-Promotion - Pay Change	SAL-Salary	126.9232
52	7/1/2017	PAYMER-Annual Increase	SAL-Salary	130.7309
58	7/1/2017	PAYMER-Annual Increase	SAL-Salary	50.395
58	7/1/2018	PAYMER-Annual Increase	SAL-Salary	57.1029
58	7/1/2016	PAYMER-Annual Increase	SAL-Salary	48.9272
58	7/1/2019	PAYMER-Annual Increase	SAL-Salary	59.2443
58	3/29/2021	PROINPOS-Promotion - In-Position	SAL-Salary	70.9479
58	6/18/2018	PROPWPHR-Promotion - Pay Change	SAL-Salary	55.4397

Now here's my challenge, for each unique user pay info combination (for each of the 4,400 rows) I want to also have access to the other employee / job info data fields like e.g . Job Title or Weekly Work Hours or Job Band etc. so I want to FILTER or INTERSECT the original table (13,000 rows) by the unique value combinations contained IN this table.

I thought I could do this using CALCULATETABLE and passing the promotabledistinct variable as a filter argument into that function like so:

CALCULATETABLE( largesourcetable, uniqueuserpayrecords )

but that function returns all 13,000 rows - didn't filter the table at all. Is this a problem with my syntax? I was hoping I could just throw a table into the filter argument and it would magically work.

One workaround I thought of would be to create a concatenated columns of all 5 columns so that I could use a combination of FILTER and uniquevalues IN concatenatedidentifier column but I was hoping to learn how this is usually done. I suppose I could try using an INTERSEC of some sort of JOIN but the problem again is that it's not just one identifier that is unique but a unique combination of 5 columns that is the unique identifier for each row.

Please, let me know where I strayed off the path....

Thank you,

Henrik

[waeltken]

Hi Parry, thank you your response. 

I'm trying to filter my 13,000 row table down to the 4,400 rows where the combined / concatenated 5 columns [User Id] [Event Date] [Event Reason] [Pay Component] [Amount] are unique / deduplicated. I cannot user Power Query, it should be in a DAX function that results in the deduplicated table.  I will use that table in a new calculated table with relationships to count / average / categorize these unique pay-related actions in given time periods like the Fiscal Year etc.

[AlexisOlson]

I think the easiest way to do this would be with SUMMARIZECOLUMNS where you include all the columns you want to return:

uniqueuserpayrecords =
SUMMARIZECOLUMNS (
    Promotions[User Id],
    Promotions[RecurringPay_Event_Date],
    Promotions[CompInfo_Event Reason],
    Promotions[Pay Component],
    Promotions[HourlyRate],
    Promotions[Job Title],
    Promotions[Work Hours],
    Promotions[Job Band]
)

IF Job Title, Work Hours, and Job Band only have a single value for each combination of the first five columns, this should be what you're after. If, not you'll need to figure out how to pick which value you want.

As for why your approach doesn't work (one reason, anyway), think about what the filter is doing. Let's say you write

CALCULATETABLE ( Promotions, DISTINCT ( Promotions[ID] ) )

What do you expect this to do?

[parry2k]

@waeltken you cannot pass dynamic filter to the calculated table. Although you have provided great details in your question but still not clear what you are trying to achieve. It will be easier if you throw sample data (or pbix) with the relationship model you have and the expected output, it will help to provide the help.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡