Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, apologies if the subject isn't clear.
I have attached a link to a data file (I can't share my real scenario due to type of data).
My data comes from a CRM system, each row is a 'Submission', I have appended the data to ensure I have one column for each category (?) of data however to improve UX in the CRM, we no longer capture all the same data for one of the 'submissions'. E.g. Submission form 1 - has 15 fields of data captured, but submission form 5 is used specifically for an 'added extra'.
The added extra form only has 5 fields in the CRM, however the added extra is only sold with a specific thing.
Please see the example data file to help this make sense.
I am looking for someone to advise how I can 'look up' the data e.g.
Value of sale =
If SALE TYPE <> "Dog" then [SALE TYPE] else if
[SALE TYPE] = "Dog" then FIND (?) matching ID and "Cat" and use that rows SALE TYPE
I hope this makes sense and I have given enough detail of what I am looking for.
Many thanks in advance
Dogs and Cats example data.xlsx
Solved! Go to Solution.
Hi, @Aimeeclaird , you might want to try translating your pseudo-code with DAX syntax in a calculated column
Lookup =
IF (
Sales[Sale Type] = "Dog",
MAXX (
FILTER ( Sales, Sales[ID] = EARLIER ( Sales[ID] ) && Sales[Sale Type] = "Cat" ),
Sales[Date of Sale]
)
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@Aimeeclaird I would use a SWITCH statement and then this will provide you some syntax for how to "lookup" values that need multiple criteria.
LOOKUPVALUE Range - Microsoft Power BI Community
Hi @Greg_Deckler
Sorry, I am still struggling with this. I've tried adapting the dax formula you shared and to incorporate SWITCH however I am not sure I understand properly so i'm not using the formula correctly.
How do I write:
If [Sale Type] = "Dog"
Find where [ID] = [ID] AND [Sale Type] = "Cat"
Return [Date of Sale] from matching row
Any help or direction to an artical that explains would be great.
Thanks in advance
Hi, @Aimeeclaird , you might want to try translating your pseudo-code with DAX syntax in a calculated column
Lookup =
IF (
Sales[Sale Type] = "Dog",
MAXX (
FILTER ( Sales, Sales[ID] = EARLIER ( Sales[ID] ) && Sales[Sale Type] = "Cat" ),
Sales[Date of Sale]
)
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thank you so much @CNENFRNL for helping me translate what I needed! Greatly appreciated.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.