Forum Discussion
Post PatronFind 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
- Jeanxyz
Power Participant
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.
patri0t82Thank 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!
- Jeanxyz
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]))
- patri0t82
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.- patri0t82
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] )))))