Forum Discussion
Find Value from Column based on Two Other Columns
Hello,
I need to create a measure that will find the value from
CALCULATE (
VALUES ( 'Targets - LTIR (Tabular)'[Value] ),
FILTER ( ALL ( 'Targets - LTIR (Tabular)' ), 'Targets - LTIR (Tabular)'[Month / Year] = MAX ( 'Targets - LTIR (Tabular)'[Month / Year] ) ),
FILTER ( ALL ( 'Targets - LTIR (Tabular)' ), 'Targets - LTIR (Tabular)'[Operating Area] = MAX ( 'Targets - LTIR (Tabular)'[Operating Area] ) )
It looks if you use values(), you can only reference a column or a table, a table expression such as filter() is not allowed. By the way, the output of values() can be a list or scalar data. Are you expecting scalar data? If that's the case, you can try the measure below.
LTIR Target NEW2 = CALCULATE(VALUES('Targets - LTIR (Tabular)'[Value]), FILTER(ALL('Targets - LTIR (Tabular)'),'Targets - LTIR (Tabular)'[Month / Year] = MAX ( 'Targets - LTIR (Tabular)'[Month / Year] )),ALLEXCEPT( 'Targets - LTIR (Tabular)','Targets - LTIR (Tabular)'[Operating Area]))If you are expecting a list from values, you might try a turnaround like the one belowLTIR Target NEW Tab =DISTINCT(FILTER(ALL('Targets - LTIR (Tabular)'),'Targets - LTIR (Tabular)'[Month / Year] = MAX ( 'Targets - LTIR (Tabular)'[Month / Year] ) && 'Targets - LTIR (Tabular)'[Operating Area]=MAX('Targets - LTIR (Tabular)'[Operating Area])))I have created a test file. Here is the link.
5 Replies
It looks if you use values(), you can only reference a column or a table, a table expression such as filter() is not allowed. By the way, the output of values() can be a list or scalar data. Are you expecting scalar data? If that's the case, you can try the measure below.
LTIR Target NEW2 = CALCULATE(VALUES('Targets - LTIR (Tabular)'[Value]), FILTER(ALL('Targets - LTIR (Tabular)'),'Targets - LTIR (Tabular)'[Month / Year] = MAX ( 'Targets - LTIR (Tabular)'[Month / Year] )),ALLEXCEPT( 'Targets - LTIR (Tabular)','Targets - LTIR (Tabular)'[Operating Area]))If you are expecting a list from values, you might try a turnaround like the one belowLTIR Target NEW Tab =DISTINCT(FILTER(ALL('Targets - LTIR (Tabular)'),'Targets - LTIR (Tabular)'[Month / Year] = MAX ( 'Targets - LTIR (Tabular)'[Month / Year] ) && 'Targets - LTIR (Tabular)'[Operating Area]=MAX('Targets - LTIR (Tabular)'[Operating Area])))I have created a test file. Here is the link.Thank you so much! Your file really helped. This is the formula I ended up using:
LTIR Target NEW =CALCULATE (MAX ( 'Targets - LTIR (Tabular)'[Value] ),FILTER (ALL ( 'Targets - LTIR (Tabular)' ),'Targets - LTIR (Tabular)'[Month / Year]= MAX ( 'Targets - LTIR (Tabular)'[Month / Year] )),FILTER (ALL ( 'Targets - LTIR (Tabular)' ),'Targets - LTIR (Tabular)'[Operating Area]= MAX ( 'Targets - LTIR (Tabular)'[Operating Area] )))
If I could ask just one more thing.. If I have no Operating Area selected I need it to represent 'CompanyX'.For more context; our company (CompanyX) is made up of several Operating Areas. If no operating area is selected, it needs to look up CompanyX from the table.
Thank you again so much!
Do you have more than one Operating Area selected? In your measure, if you select more than one Operating Area, only largest one is considered as a filter condition. Try if the one below meets your need
LTIR Target NEW =
CALCULATE (
VALUES ( 'Targets - LTIR (Tabular)'[Value] ),
FILTER ( ALL ( 'Targets - LTIR (Tabular)' ), 'Targets - LTIR (Tabular)'[Month / Year] = MAX ( 'Targets - LTIR (Tabular)'[Month / Year] ) ),
ALLEXCEPT( 'Targets - LTIR (Tabular)' ), 'Targets - LTIR (Tabular)'[Operating Area]))
Thank you so much for the response. Yes I had one Operating Area selected
I tried your formula, though there appears to be an extra ) after the first Tabular in your ALLEXCEPT row.
This error is returned in my card
MdxScript(Model) (416, 1) Calculation error in measure 'Calculations'[LTIR Target NEW]: A table of multiple values was supplied where a single value was expected.Hi again, I just wanted to follow up. I think I've got the final solution. It's a bit beastly but it's working well. There was more than CompanyX. It was also CompanyX1, X2, X3. Here's the final code - and thank you again so much!
LTIR Target =IF (SELECTEDVALUE ( 'Operating Areas and Value Centres'[Value Centre] ) <> "CompanyX1" &&SELECTEDVALUE ( 'Operating Areas and Value Centres'[Value Centre] ) <> "CompanyX2" &&SELECTEDVALUE ( 'Operating Areas and Value Centres'[Value Centre] ) <> "CompanyX3",CALCULATE (MAX ( 'Targets - LTIR (Tabular)'[Value] ),FILTER (ALL ( 'Targets - LTIR (Tabular)' ),'Targets - LTIR (Tabular)'[Month / Year]= MAX ( 'Targets - LTIR (Tabular)'[Month / Year] )),FILTER ('Operating Areas and Value Centres','Operating Areas and Value Centres'[Value Centre] = "CompanyX")),IF (SELECTEDVALUE ( 'Operating Areas and Value Centres'[Operating Area] ) = "",CALCULATE (MAX ( 'Targets - LTIR (Tabular)'[Value] ),FILTER (ALL ( 'Targets - LTIR (Tabular)' ),'Targets - LTIR (Tabular)'[Month / Year]= MAX ( 'Targets - LTIR (Tabular)'[Month / Year] )),FILTER (ALL ( 'Targets - LTIR (Tabular)' ),'Targets - LTIR (Tabular)'[Operating Area]= SELECTEDVALUE ( 'Operating Areas and Value Centres'[Value Centre] ))),CALCULATE (MAX ( 'Targets - LTIR (Tabular)'[Value] ),FILTER (ALL ( 'Targets - LTIR (Tabular)' ),'Targets - LTIR (Tabular)'[Month / Year]= MAX ( 'Targets - LTIR (Tabular)'[Month / Year] )),FILTER (ALL ( 'Targets - LTIR (Tabular)' ),'Targets - LTIR (Tabular)'[Operating Area]= MAX ( 'Targets - LTIR (Tabular)'[Operating Area] )))))
