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.
Hello,
I have a table of data that look like the below:
Dates | Area | To display? |
01-Jan-20 | 1 | No |
02-Jan-20 | 1 | No |
03-Jan-20 | 1 | No |
04-Jan-20 | 1 | No |
05-Jan-20 | 1 | Yes |
06-Jan-20 | 1 | No |
07-Jan-20 | 1 | No |
08-Jan-20 | 1 | No |
09-Jan-20 | 1 | Yes |
01-Jan-20 | 2 | No |
02-Jan-20 | 2 | No |
03-Jan-20 | 2 | Yes |
04-Jan-20 | 2 | No |
05-Jan-20 | 2 | No |
06-Jan-20 | 2 | Yes |
07-Jan-20 | 2 | No |
08-Jan-20 | 2 | No |
09-Jan-20 | 2 | No |
01-Jan-20 | 3 | Yes |
02-Jan-20 | 3 | Yes |
03-Jan-20 | 3 | No |
04-Jan-20 | 3 | No |
05-Jan-20 | 3 | No |
06-Jan-20 | 3 | No |
07-Jan-20 | 3 | No |
08-Jan-20 | 3 | No |
09-Jan-20 | 3 | No |
I want to create a table so it returns the most recent date with a "Yes". The resulting table should look like this:
Area | Yes |
1 | 09-Jan-20 |
2 | 06-Jan-20 |
3 | 02-Jan-20 |
How can I accomplish this?
Solved! Go to Solution.
Hi @Anonymous ,
How about this?
MAX_DATE =
CALCULATE (
MAX ( YESTABLE[Dates] ),
ALLEXCEPT ( GEOGRAPHY, GEOGRAPHY[AREA] ),
YESTABLE[To display] = "Yes"
)
If it doesn't work, please share us some sample data of your two tables, removing sensitive information.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
How about this?
MAX_DATE =
CALCULATE (
MAX ( YESTABLE[Dates] ),
ALLEXCEPT ( GEOGRAPHY, GEOGRAPHY[AREA] ),
YESTABLE[To display] = "Yes"
)
If it doesn't work, please share us some sample data of your two tables, removing sensitive information.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
I would use
MAX_DATE = CALCULATE(MAX(table[Dates]),FILTER(ALLEXCEPT(table,table[Area]),table[To display] = "Yes")
and plot it using matrix
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
Thank you. My issue is that Table[area] isn't part of the Table. "Area" comes from another table and is brought here through a relationship.
HI @Anonymous
In that case you need to create a relationship between both tables.
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
The relationship has already been created, so how should I re-write this part of the code?
FILTER(ALLEXCEPT(YESTABLE, GEOGRAPHY[AREA]) doesn't seem to work.
FILTER(ALLEXCEPT(table,table[Area]
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 |
---|---|
106 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |