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 everyone,
I have a report in which the user can see orders information for certain contracts that they can select in a slicer.
I have two tables:
1. An "Orders" table, with all the orders information and, for each order, the contract ID it is assigned to.
2. A "Contracts" table, with all the contract information including its start and end date.
There is a Many-to-One relationship between these tables, based on "Contract ID".
In order to create a chart that shows the combined orders quantity for all selected contracts, I would like to find out the minimum start and end dates across all the selected contracts.
For example, if the user selects contracts A, B, and C in the slicer, I would like to see the following information:
Contract ID | Contract | Start Date | End Date | Min Start Date | End Start Date |
1234 | A | 01/01/17 | 31/12/17 | 01/01/17 | 30/06/19 |
1235 | B | 06/01/18 | 31/12/18 | 01/01/17 | 30/06/19 |
1236 | C | 01/01/19 | 30/06/19 | 01/01/17 | 30/06/19 |
I am guessing it cannot be that complex, but I have still been stuck on this for quite some time... I am mainly struggling with the fact that there is no common column to relate these values.
Any help would be greatly appreciated!
Many thanks in advance, and take care.
@inesj , Create two measures like
Min Start Date = minx(Allselected(Table), Table[Start Date])
End Start Date = maxx(Allselected(Table), Table[End Date])
If from contracts, then
Min Start Date = minx(Allselected(contract), contract[Start Date])
End Start Date = maxx(Allselected(contract), contract[End Date])
Hi @inesj
Create measures:
Min Start Date =
CALCULATE (
MIN ( Contracts[Contract Start Date] ),
ALLSELECTED ( Contracts[Contract] ),
ALL ( Contracts )
)
End Start Date =
CALCULATE (
MAX ( Contracts[Contract End Date] ),
ALLSELECTED ( Contracts[Contract] ),
ALL ( Contracts )
)
If this doesn't work please share the pbix (with dummy data if necessary) that reproduces the issue
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |