topic Re: How to limit one measure based on the other measure for total numbers in DAX Commands and Tips

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

SLucky — Wed, 01 Jun 2022 04:05:56 GMT

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

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

tamerj1 — Wed, 01 Jun 2022 05:28:07 GMT

Hi SLucky

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

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

SLucky — Wed, 01 Jun 2022 05:35:37 GMT

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

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

tamerj1 — Wed, 01 Jun 2022 06:26:14 GMT

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

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

SLucky — Wed, 01 Jun 2022 14:39:01 GMT

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!

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

SLucky — Wed, 01 Jun 2022 14:40:00 GMT

Hi Anonymous

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!

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

tamerj1 — Wed, 01 Jun 2022 14:46:02 GMT

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?

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

SLucky — Wed, 01 Jun 2022 14:54:16 GMT

Hi tamerj1

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

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

tamerj1 — Wed, 01 Jun 2022 14:55:31 GMT

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

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

SLucky — Wed, 01 Jun 2022 15:10:25 GMT

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.

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

tamerj1 — Wed, 01 Jun 2022 15:26:29 GMT

SLucky

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

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

SLucky — Wed, 01 Jun 2022 15:37:45 GMT

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.

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

tamerj1 — Wed, 01 Jun 2022 15:43:59 GMT

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?

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

SLucky — Wed, 01 Jun 2022 15:50:16 GMT

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

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

tamerj1 — Wed, 01 Jun 2022 16:09:21 GMT

SLucky

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

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

SLucky — Wed, 01 Jun 2022 16:14:41 GMT

Hi tamerj1

Yes, I am using a slicer.

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

SLucky — Wed, 01 Jun 2022 16:17:40 GMT

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)

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

tamerj1 — Wed, 01 Jun 2022 16:42:58 GMT

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

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

SLucky — Wed, 01 Jun 2022 17:23:58 GMT

Thank you so much tamerj1

It works!

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

Anonymous — Fri, 03 Jun 2022 05:35:35 GMT

is it good now SLucky