Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Good morning Power BI experts!
I Probably should be able to figure it out but i am unfortunately not. I have two tables. One is a simple volume report shown here:
Volume Report | |||||
Region | Item Code | Invoice Date | DC | Supplier | Volume |
West | 123 | 12/1/2021 | Miami | Supplier A | 876 |
East | 987 | 1/13/2021 | San Diego | Supplier B | 219 |
North | 55 | 6/5/2021 | Pittsburg | Supplier F | 142 |
South | 466 | 1/22/2020 | Denver | Supplier Z | 630 |
West | 5428 | 9/8/2017 | Butte | Supplier A | 721 |
I also have a "Project List" report that I want to query the Volume report to bring back the associated quantities. There can and will be overlap of volumes from the Volume Report going into the Project List. What I am struggling with is I have 4 different criteria:
Region | Project | Item Code | Start Date | End Date | Specific DC | Specific Supplier | Volume | Notes |
West | Apples | 123 | 12/1/2021 | 1/1/2022 | Want total volume for that region and Item Code between the two dates (all DCs and All Suppliers) | |||
East | Pears | 456 | 10/1/2021 | Supplier A | Want total volume for that region and item code between the two dates (all DCs but only "Supplier A") | |||
West | Apples | 123 | 1/1/2022 | 12/31/2022 | Miami | Want total volume for that region and item code between the two dates (all Suppliers but only the "Miami" DC | ||
East | Peaches | 987 | 7/1/2021 | Supplier C | San Diego | Want total volume for that region, item code, specific DC and Specific Supplier between the two dates |
1. If the Specific Supplier and Specific DC are populated on the Project List, i want the total volume for that region, item code, specific DC and Specific Supplier between the two dates
2. If the Specific Supplier is populated but not the Specific DC on the Project List, I want to bring back the total volume for that region, item code, specific Supplier across all DCs between the two dates
3. If the Specific DC is populated but not the Specific Supplier on the Project List, i want to bring back the total volume for that region, item code, specific DC across all Suppliers between the two dates
4. If neither the Specific Supplier or Specific DC are populated on the Project List, I want to bring back the total volume for that region, item code across all Suppliers and DCs between the two dates.
Do i need to break the item list into different tables to seperate the different criteria or something else?
Thanks!
Solved! Go to Solution.
I think you could do this with a measure along these lines:
TotalVolume =
VAR NoDC = ISBLANK ( MAX ( Project[Specific DC] ) )
VAR NoSupplier = ISBLANK ( MAX ( Project[Specific Supplier] ) )
VAR StartDate = MIN ( Project[Start Date] )
VAR EndDate = MAX ( Project[End Date] )
RETURN
CALCULATE (
SUM ( Volume[Volume] ),
Volume[Region] IN VALUES ( Project[Region] ),
Volume[Item Code] IN VALUES ( Project[Item Code] ),
Volume[Invoice Date] <= EndDate,
Volume[Invoice Date] >= StartDate,
FILTER (
VALUES ( Volume[DC] ),
NoDC || Volume[DC] IN VALUES ( Project[Specific DC] )
),
FILTER (
VALUES ( Volume[Supplier] ),
NoSupplier || Volume[Supplier] IN VALUES ( Project[Specific Supplier] )
)
)
I think you could do this with a measure along these lines:
TotalVolume =
VAR NoDC = ISBLANK ( MAX ( Project[Specific DC] ) )
VAR NoSupplier = ISBLANK ( MAX ( Project[Specific Supplier] ) )
VAR StartDate = MIN ( Project[Start Date] )
VAR EndDate = MAX ( Project[End Date] )
RETURN
CALCULATE (
SUM ( Volume[Volume] ),
Volume[Region] IN VALUES ( Project[Region] ),
Volume[Item Code] IN VALUES ( Project[Item Code] ),
Volume[Invoice Date] <= EndDate,
Volume[Invoice Date] >= StartDate,
FILTER (
VALUES ( Volume[DC] ),
NoDC || Volume[DC] IN VALUES ( Project[Specific DC] )
),
FILTER (
VALUES ( Volume[Supplier] ),
NoSupplier || Volume[Supplier] IN VALUES ( Project[Specific Supplier] )
)
)
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
72 | |
38 | |
31 | |
26 |
User | Count |
---|---|
97 | |
86 | |
43 | |
40 | |
35 |