The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi,
I'm producing schedule portfolio reports.
I'm trying to sum given a certain code condition and excepting the project filter on the data which comes from a related table.
This works:
Thanks in advance for all helpful responses.
Solved! Go to Solution.
Hi @kylee_anne ,
To sum values in the Resource B table for rows where [Spreadsheet Field] equals "Budgeted Units," but preserve the filter context from a related field ([Key + Title] in the Portfolio List table), you should use ALL to remove all filters from Resource B, and VALUES to keep the filter for [Key + Title] from the related table.
You cannot use RELATED or columns from related tables inside ALLEXCEPT, so this combination achieves your goal: the measure always sums by "Budgeted Units" and only changes when the [Key + Title] from Portfolio List changes in your report.
Use this code:
PVT =
CALCULATE(
SUM('Resource B'[Value]),
'Resource B'[Spreadsheet Field] = "Budgeted Units",
ALL('Resource B'),
VALUES('Portfolio List'[Key + Title])
)
ALL('Resource B') removes all filters from the fact table. VALUES('Portfolio List'[Key + Title]) reapplies only the desired filter from your related dimension table, so your visual responds to this context as expected.
PVT := // try with treatas might help you
CALCULATE(
SUM('Resource B'[Value]),
'Resource B'[Spreadsheet Field] = "Budgeted Units",
TREATAS(
VALUES('Portfolio List'[Key + Title]),
'Resource B'[Key]
)
)
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
This is a DAX question, pls post it in the DAX forum
https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/bd-p/DAXCommands
Meanwhile, the answer 🙂
PVT =
CALCULATE(
sum('Resource B'[Value]),
'Resource B'[Spreadsheet Field]="Budgeted Units",
ALLEXCEPT('Resource B','Resource B'[Key]), PortfolioList[Title])
)
this assumes there is a one to many relationship between the two tables as I see from the picture
You do not need RELATED since the expanded table will make the relates columns active by default
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Hi @kylee_anne ,
To sum values in the Resource B table for rows where [Spreadsheet Field] equals "Budgeted Units," but preserve the filter context from a related field ([Key + Title] in the Portfolio List table), you should use ALL to remove all filters from Resource B, and VALUES to keep the filter for [Key + Title] from the related table.
You cannot use RELATED or columns from related tables inside ALLEXCEPT, so this combination achieves your goal: the measure always sums by "Budgeted Units" and only changes when the [Key + Title] from Portfolio List changes in your report.
Use this code:
PVT =
CALCULATE(
SUM('Resource B'[Value]),
'Resource B'[Spreadsheet Field] = "Budgeted Units",
ALL('Resource B'),
VALUES('Portfolio List'[Key + Title])
)
ALL('Resource B') removes all filters from the fact table. VALUES('Portfolio List'[Key + Title]) reapplies only the desired filter from your related dimension table, so your visual responds to this context as expected.
You do not need a RELATED call into ALLEXCEPT to mention columns in related tables (one to many), but you can avoid removing the filter instead of removing it and then reapplying that with VALUES.
The two measures would be different, of course. I only want to clarify that it is not that you cannot use RELATED, you do not need RELATED to write the below code (expanded tables). If you need the syntax with VALUES, OK but that is another point (you are sure with that syntax that a filter is applied to the column even though it was not present at the beginning)
Just to make sure on the DAX syntax
PVT =
CALCULATE(
sum('Resource B'[Value]),
'Resource B'[Spreadsheet Field]="Budgeted Units",
ALLEXCEPT('Resource B','Resource B'[Key]), PortfolioList[Title])
)
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI