Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
SLucky
Helper I
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!!

SLucky_0-1654055925803.png

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

View solution in original post

20 REPLIES 20
Datagulf
Responsive Resident
Responsive Resident

is it good now @SLucky 

Yes, thanks!

tamerj1
Super User
Super User

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

Thank you so much @tamerj1 

 

It works!

SLucky
Helper I
Helper I

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!

SLucky_1-1654094366068.png

 

 

SLucky
Helper I
Helper I

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!

SLucky_0-1654094269205.png

 

@SLucky 

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?

Hi @tamerj1 

 

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

@SLucky 

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.

@SLucky 

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.

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.

@SLucky 

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?

@tamerj1 

 

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

@SLucky 

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)

Hi @tamerj1 

 

Yes, I am using a slicer.

tamerj1
Super User
Super User

Hi @SLucky 

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

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

@SLucky 

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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