Forum Discussion
Iteration formulas
Hello
I post again as it seems after the first reply, no one comes back to the initial post to provide clarifications.
I need a formula that will find all matches and return a specific value out of the list/table of matches.
As an example, I have this Table1:
A, text1, 2/1/19
A, text2, 3/1/19
B, text3, 1/1/19
...
I will need to create this Table2:
A, text2
B, text3
The formula will look up Table2[Col1] in Table1[Col1] and then check the LATEST date in the matches and return the Table1[Col2] of the latest date.
I need a formula to specify:
1) the lookup value (this will be a column reference)
2) the column to search for that lookup value to find all the matches
3) the column to use to do the sorting (the column with the dates)
4) the value to return (column reference)
How can all these be specified in a Measure?
Also, it would be good to know, how to return a specific value when among the matches, there is a specific value. Any idea?
Thanks!
Hi Anonymous ,
Use:
Column = VAR a = CALCULATE ( MAX ( 'Table'[Column3] ), 'Table'[Column1] = EARLIER ( Table2[Column1] ) ) RETURN CONCATENATEX ( FILTER ( ALL ( Table ), 'Table'[Column1] = 'Table2'[Column1] && 'Table'[Column3] = a ), 'Table'[Column2], " , " )Please refer to :http://www.excelnaccess.com/dealing-with-duplicates-a-table-of-multiple-values-was-supplied-using-dax/
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
5 Replies
Anonymous it should be easy, add the following measure
Latest text = VAR __latestDate = CALCULATE ( MAX ( Table[Date] ), ALLEXCEPT ( Table, Table[ID] ) ) RETURN CALCULATE ( MAX ( Table[Text] ), ALLEXCEPT ( Table, Table[ID] ), Table[Date] = __latestDate )I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
This may be useful here:
https://stackoverflow.com/questions/52525377/return-top-value-ordered-by-another-column
Hi Anonymous ,
You can use the following calculated column:
Column = VAR a = CALCULATE ( MAX ( 'Table'[Column3] ), 'Table'[Column1] = EARLIER ( Table2[Column1] ) ) RETURN LOOKUPVALUE ( 'Table'[Column2], 'Table'[Column1], Table2[Column1], 'Table'[Column3], a )For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ER7qnc-xXbZPno6oHUUZdHYBpTtss0Dq0FVX8PvD67cl2g?e=WuMjZq
Best Regards,
Dedmon Dai
Thanks but I get the error 'table returned when a single value was expected'.
Any idea?
Hi Anonymous ,
Use:
Column = VAR a = CALCULATE ( MAX ( 'Table'[Column3] ), 'Table'[Column1] = EARLIER ( Table2[Column1] ) ) RETURN CONCATENATEX ( FILTER ( ALL ( Table ), 'Table'[Column1] = 'Table2'[Column1] && 'Table'[Column3] = a ), 'Table'[Column2], " , " )Please refer to :http://www.excelnaccess.com/dealing-with-duplicates-a-table-of-multiple-values-was-supplied-using-dax/
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
