cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## How to limit one measure based on the other measure for total numbers

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!!

1 ACCEPTED SOLUTION
Super User

Hi @SLucky

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 )
)
20 REPLIES 20
Responsive Resident

is it good now @SLucky

Helper I

Yes, thanks!

Super User

Hi @SLucky

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 )
)
Helper I

Thank you so much @tamerj1

It works!

Helper I

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!

Helper I

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!

Super User

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?

Helper I

Hi @tamerj1

Thanks for your quick response! I appreciated it. I haven't use the code.

Super User

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 )
)
Helper I

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.

Super User

Can you explain step by step how we're supposed to reach to this number ( -\$18,783 )

Helper I

Hi @tamerj1

Sorry I am not saying clear.

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.

Super User

1. Share a screenshot of your data model with the relationships?

2. Share the code of both 2019 and 2022 measures?

Helper I

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(

'Calendar Dates'[Date], -3, year))
Super User

Sorry for asking too many questions but how do you filter for the year 2022? Are you using a slicer?

Helper I

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)

Helper I

Hi @tamerj1

Yes, I am using a slicer.

Super User

Hi @SLucky

is the date from the same table or you have a date table?

Helper I

Date and Code are columns from two different tables. The other two are measures

Super User

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 )
)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors