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 Guys,
I need some help on this. Here's my data:
Date | Store | Product | Answer |
16/07/2018 | McDonald | Pizza | Yes |
17/07/2018 | McDonald | Pizza | No |
16/07/2018 | McDonald | Burger | No |
17/07/2018 | McDonald | Burger | No |
16/07/2018 | KFC | Pizza | Yes |
17/07/2018 | KFC | Pizza | Yes |
16/07/2018 | KFC | Burger | No |
17/07/2018 | KFC | Burger | Yes |
I need to always get the Answer for latest Date. Here what result should be:
Date | Store | Product | Answer |
17/07/2018 | McDonald | Pizza | No |
17/07/2018 | McDonald | Burger | No |
17/07/2018 | KFC | Pizza | Yes |
17/07/2018 | KFC | Burger | Yes |
1. Can you please tell me the DAX formula to create above table?
2. Can you please tell me what should be the Measure for using Answer in a visualisation?
Thanks in advance.
Rob
Solved! Go to Solution.
Hi,
You may download my file from here.
Hope this helps.
@Anonymous
As a NEW Table ("Calculated Table"), you can use
New Table = VAR temp = ADDCOLUMNS ( Table1, "RANK", RANKX ( FILTER ( Table1, [Store] = EARLIER ( [Store] ) ), [Date],, DESC, DENSE ) ) RETURN FILTER ( temp, [RANK] = 1 )
Hi,
You may download my file from here.
Hope this helps.
Hi Ashish,
Thanks for your help.
However, I need a measure that will display the value for Answer in any visualisation. Is that possible?
And/or create a new table in DAX that filters the below data only:
Date | Store | Product | Answer |
17/07/2018 | McDonald | Pizza | No |
17/07/2018 | McDonald | Burger | No |
17/07/2018 | KFC | Pizza | Yes |
17/07/2018 | KFC | Burger | Yes |
Thanks
Hi,
That is what my solution does. Perhaps i do not understand your requirement.
Hi Ashish,
Can you please let me know the syntax for producing the below new table in DAX?
Using this feature:
Date | Store | Product | Answer |
17/07/2018 | McDonald | Pizza | No |
17/07/2018 | McDonald | Burger | No |
17/07/2018 | KFC | Pizza | Yes |
17/07/2018 | KFC | Burger | Yes |
Thank you
@Anonymous
As a NEW Table ("Calculated Table"), you can use
New Table = VAR temp = ADDCOLUMNS ( Table1, "RANK", RANKX ( FILTER ( Table1, [Store] = EARLIER ( [Store] ) ), [Date],, DESC, DENSE ) ) RETURN FILTER ( temp, [RANK] = 1 )
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.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |