Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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?
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |