Error with measure in table but not chart

[JacobD]

Hello! 

I am having an issue where a measure I am using is working fine on a line and clustered column chart but gives me an error when used in a table. I get the following error: 

The measure I am trying to use in the table is NOT the measure referenced in the error. However, the measure referenced in the error does have an effect on the measure I am trying to use in the table. This is the measure I am getting an error with:

Total Budget Customer Cases = if(and(VALUES(Dateformat[Week])>13,max('PB: Customer Master'[Sales Rep Index])="1_1_7_415"),0,if(and(VALUES(Dateformat[Week])>13,max('PB: Customer Master'[Sales Rep Index])="1_1_7_456"),0,CALCULATE(SUM('PB: Customer Master'[Budgeted Cases]))))

The measure used to be:

Total Budget Customer Cases = CALCULATE(SUM('PB: Customer Master'[Budgeted Cases]))

And worked fine when it was but we needed to make a change to it.

Unfortunately there is not much more I can add to this, I am unable to really put the file out there. I'm hoping someone can still help.

Thanks in advance!

[parry2k]

@JacobD if you can share some data with the expected output it will help to provide a solution.

[parry2k]

@JacobD what are you trying to achieve? Let's start there.

[JacobD]

@parry2k The customer Budgeted Cases is a column that we end multiplying times a weight for a specific week. However, we want the budgeted cases to stop for those two indexes in the if statement starting with week 13. However, when I add that if statement, that's when the errors start.

[v-linyulu-msft · Accepted solution]

Thanks for the reply from @parry2k , please allow me to provide another insight:
Hi,@JacobD 

Regarding the issue you raised, my solution is as follows:
1.First I have created the following table and the column names and data are the data you have given:

In my testing, I had the same problem:

2.Relate the two tables and reference the week column to this table:

week1 = LOOKUPVALUE(Dateformat[week],'Dateformat'[Sales Rep Index],'PB： Customer Master'[Sales Rep Index])

3. Below are the measure I've created for your needs:

Measure = CALCULATE(SUM('PB： Customer Master'[Budgeted Cases]),FILTER('PB： Customer Master',NOT(AND('PB： Customer Master'[week1]>13,'PB： Customer Master'[Sales Rep Index]="1_1_7_415"||'PB： Customer Master'[Sales Rep Index]="1_1_7_456"))))

Here is another measure if you want to implement accumulation:

Measure 2 = CALCULATE(SUM('PB： Customer Master'[Budgeted Cases]),FILTER(ALLSELECTED('PB： Customer Master'),NOT(AND('PB： Customer Master'[week1]>13,'PB： Customer Master'[Sales Rep Index]="1_1_7_415"||'PB： Customer Master'[Sales Rep Index]="1_1_7_456"))&&'PB： Customer Master'[week1]<=MAX('PB： Customer Master'[week1])))

4.Here's my final result, which I hope meets your requirements.

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.
Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@JacobD what it the purpose of using values function? 

[JacobD]

The measure didn't work without it. I received an error stating "a single value for column 'Week' in table 'Dateformat' cannot be determined"