Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
I would like to limit the total number of "2022 Labor Expense" based on non-blank values in "2019 Labor Expense".
For Example we have data as below. Left hand side is the original view with company breakdown. When I add the filter for non-blank of "2019 Labor Expense", the total of "2022 Labor Expense" changes to -$18,783 which is the number I want.
My question is if I remove "code" variable (it is company name) to show the total, and add filter for "2019 Labor Expense" to the view, the total of "2022 Labor Expense" stays the same as -$35,565. How can I make it to -$18,783? Thanks!!
Solved! Go to Solution.
Hi @SLucky
please try
2022 Labor Expense Filterd = VAR Codes2019 = SELECTCOLUMNS ( CALCULATETABLE ( 'Labor Data', DATEADD ( 'Calendar Dates'[Date], -3, YEAR ) ), "@Code", 'Labor Data'[Code] ) RETURN CALCULATE ( [2022 Labor Expense], FILTER ( 'Company Code', 'Company Code'[Code] IN Codes2019 ) )
Yes, thanks!
Hi @SLucky
please try
2022 Labor Expense Filterd = VAR Codes2019 = SELECTCOLUMNS ( CALCULATETABLE ( 'Labor Data', DATEADD ( 'Calendar Dates'[Date], -3, YEAR ) ), "@Code", 'Labor Data'[Code] ) RETURN CALCULATE ( [2022 Labor Expense], FILTER ( 'Company Code', 'Company Code'[Code] IN Codes2019 ) )
Hi @Datagulf
Move conversation here. I would like to see the total of 2022 labor expense but filtered with individual company that has value in 2019 labor expense. The final report only has one line. Format sample see below(2022 labor expense should be -$18,783). Thank you!
Hi @Pragati11
Here is the problem. I would like to see the total of 2022 labor expense but filtered with individual company that has value in 2019 labor expense. The final report only has one line. Format sample see below(2022 labor expense should be -$18,783). Thank you!
This is what the measure was intended to do. However I discovered an error in the code, I will edit now but what results did you get using this code?
Ok then try this one
2022 Labor Expense Filterd =
VAR Codes2019 =
SELECTCOLUMNS (
CALCULATETABLE ( TableName, 'Date'[Year] = 2019 ),
"@Code", TableName[Code]
)
RETURN
CALCULATE (
[2022 Labor Expense],
FILTER ( TableName, TableName[Code] IN Codes2019 )
)
Hi @tamerj1
I checked the company code. Most of the companies have value in some days of 2019 while in some day they don't. If I would like to see data for all days in 2022, I cannot use the fixed list of company code.
Can you explain step by step how we're supposed to reach to this number ( -$18,783 )
Hi @tamerj1
Sorry I am not saying clear.
I have three tables: a data table, a company code table and a labor expense table.
2022 labor expense: it is the sum of the labor expense from labor expense table.
2019 labor expense: it is a dataadd measure from 2022 labor expense variable.
I would like to see the total of 2022 labor expense in any date but only calculate the company where it has value in the same day in 2019.
Let's say we have 10 companies in total. On 1/1/2019, 5 companies have value in 2019 labor expense variable. Then the total of 1/1/2022 labor expense will be the sum expense of those 5 companies. On 5/1/2019, if only 2 companies have value in 2019 labor expense, the total of 5/1/2022 labor expense will sum labor expense of those 2 companies.
Great. Can you please
1. Share a screenshot of your data model with the relationships?
2. Share the code of both 2019 and 2022 measures?
1. Table relationship: calendar table and property table don't have any relationship. Labor expense table has 1:many for calendar table(Date) and property table(Code).
2. 2022 Labor expense = SUM('Labor Data'[expense])
2019 Labor expense = calculate([2022 Labor expense], dateadd(
Sorry for asking too many questions but how do you filter for the year 2022? Are you using a slicer?
Hi @tamerj1
I just found that the relationship I mentioned above is wrong. Sorry.
Labor expense table has many:1 for calendar table(Date) and property table(Code)
Date and Code are columns from two different tables. The other two are measures
Please try
2022 Labor Expense Filterd =
VAR Codes2019 =
SELECTCOLUMNS (
FILTER ( TableName, RELATED ( 'Date'[Year] ) = 2019 ),
"@Code", TableName[Code]
)
RETURN
CALCULATE (
[2022 Labor Expense],
FILTER ( TableName, TableName[Code] IN Codes2019 )
)
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
23 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
43 | |
31 | |
24 | |
22 | |
22 |