Forum Discussion
Help with summarizing a value at higher level than table join
Hi I am reasonably experienced with DAX but I am struggling with a solution for this problem
I have 2 tables, one is my fact table which contains columns - Vendor Part Code and Warehouse Part Code
my second table, a scheduled delivery table also contains Vendor Part Code, Warehouse Part Code and a Qty to deliver value
The Warehouse Part Code is unique and the Vendor Part Code can be repeated for multiple Warehouse Partcodes
my tables are joined by the Warehouse Part Code
In my visual i list the warehouse part codes and the vendor partcodes - I am trying to create a measure that sums the delivery quantities for all the grouped vendor partcodes and list the same value against each relevant warehouse part code. I seem unable to break the link between the 2 tables being joined on warehouse part codes, I have tried numerous ways of doing this but have been unsuccesful. The simple table below is the what I would like to see
warehouse | vendor | qty
wpart1 vpart1 50
wpart2 vpart1 50
Can anyone help please - I know I could create a calculated table but I am trying to find a solution by using a measure, in case in the future I am working with a data set I can't create new tables
Hi acasburn,
Since you cannot download the PBIX on your work PC, you can do this directly with a measure.
Assuming your tables are called Fact and Scheduled Delivery:
Vendor Delivery Qty = VAR CurrentVendor = SELECTEDVALUE ( Fact[Vendor Part Code] ) RETURN CALCULATE ( SUM ( 'Scheduled Delivery'[Qty to deliver] ), REMOVEFILTERS ( Fact[Warehouse Part Code] ), REMOVEFILTERS ( 'Scheduled Delivery'[Warehouse Part Code] ), TREATAS ( { CurrentVendor }, 'Scheduled Delivery'[Vendor Part Code] ) )The key part is removing the Warehouse Part Code filter.
Without that, the relationship continues filtering Scheduled Delivery down to the individual warehouse part currently shown on the row.
TREATAS then applies the Vendor Part Code from the current row to Scheduled Delivery, so the measure returns the total quantity for that vendor.
For example, if:
wpart1 / vpart1 = 20 wpart2 / vpart1 = 30 the visual would show: wpart1 | vpart1 | 50 wpart2 | vpart1 | 50Microsoft documents REMOVEFILTERS for clearing selected filters and TREATAS for applying values from one table as filters to another.
If your actual table or column names are different, just substitute those names in the measure.
AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
7 Replies
Hi Maruthi,
Thanks for your response but I can't download on a works PC - can you share the dax code?
- ShivekMaharaj
Resident Rockstar
Hi acasburn,
Since you cannot download the PBIX on your work PC, you can do this directly with a measure.
Assuming your tables are called Fact and Scheduled Delivery:
Vendor Delivery Qty = VAR CurrentVendor = SELECTEDVALUE ( Fact[Vendor Part Code] ) RETURN CALCULATE ( SUM ( 'Scheduled Delivery'[Qty to deliver] ), REMOVEFILTERS ( Fact[Warehouse Part Code] ), REMOVEFILTERS ( 'Scheduled Delivery'[Warehouse Part Code] ), TREATAS ( { CurrentVendor }, 'Scheduled Delivery'[Vendor Part Code] ) )The key part is removing the Warehouse Part Code filter.
Without that, the relationship continues filtering Scheduled Delivery down to the individual warehouse part currently shown on the row.
TREATAS then applies the Vendor Part Code from the current row to Scheduled Delivery, so the measure returns the total quantity for that vendor.
For example, if:
wpart1 / vpart1 = 20 wpart2 / vpart1 = 30 the visual would show: wpart1 | vpart1 | 50 wpart2 | vpart1 | 50Microsoft documents REMOVEFILTERS for clearing selected filters and TREATAS for applying values from one table as filters to another.
If your actual table or column names are different, just substitute those names in the measure.
AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
- ShahRukhSameer
Impactful Individual
Hi acasburn,
I think the issue is that the current Warehouse Part Code filter is being passed through the relationship to your Scheduled Delivery table.
You can remove that filter and then apply the Vendor Part Code from the current row instead.
Assuming your tables are called Fact and Scheduled Delivery, try this:
Vendor Delivery Qty =
VAR CurrentVendor =
SELECTEDVALUE ( Fact[Vendor Part Code] )RETURN
CALCULATE (
SUM ( 'Scheduled Delivery'[Qty to deliver] ),
REMOVEFILTERS ( Fact[Warehouse Part Code] ),
REMOVEFILTERS ( 'Scheduled Delivery'[Warehouse Part Code] ),
TREATAS (
{ CurrentVendor },
'Scheduled Delivery'[Vendor Part Code]
)
)The important part here is removing the Warehouse Part Code filter. Otherwise, the relationship keeps filtering the Scheduled Delivery table down to the individual warehouse.
TREATAS then applies the Vendor Part Code from the current row, so the measure calculates the total delivery quantity for that vendor and shows the same total against each warehouse.
For example, if vpart1 has:
wpart1 - 20
wpart2 - 30The visual would show:
warehouse | vendor | qty
wpart1 | vpart1 | 50
wpart2 | vpart1 | 50So you should be able to do this with a measure without creating another table.
Thanks a lot, your solution works perfectly
- Ashish_Mathur
Super User
Hi
Please share some data to work with and show the expected result.
