topic Re: Measure to Filter and Aggregate Across 2 Fact Tables in DAX Commands and Tips

Measure to Filter and Aggregate Across 2 Fact Tables

russell80 — Mon, 13 Dec 2021 09:06:34 GMT

Looking for some help with this one. I have a simple model where I have a table of projects, called 'Projects', where [Name] is unique, and this is related to the 'Budgets' and 'Invoices' tables with one-to-many relationships. The 'Result' i am trying to get is a table visual for every 'Projects'[Name] with a measure, [Invoiced], which shows a sum of 'Invoices'[Value] for each 'Projects'[Name], for those projects that have a sum of 'Budgets'[Value] greater than 0. Hopefully that makes sense.

I got as far as the code below which works for each row in table visual, but the total is off and I can't get this one figured out. I tried adding the ALL filter to CALCULATE to restore it for the Total but that's obviously not working.

Invoiced = CALCULATE ( SUM ( 'Invoices'[Value] ), FILTER ( 'Budgets', SUM ( 'Budgets'[Values] ) > 0 ), ALL ( 'Budgets'[Name] ), CROSSFILTER ( 'Budgets'[Name], 'Projects'[Name], BOTH ) )

Re: Measure to Filter and Aggregate Across 2 Fact Tables

amitchandak — Mon, 13 Dec 2021 11:08:20 GMT

russell80 , Try a measure like

Measure =
var _inv = calculate(sum(Invoices[Value]))
var _bud = calculate(sum(Budget[Value]))
return
sumc(filter(Values(Project[project]), _bud >0),_inv)

Re: Measure to Filter and Aggregate Across 2 Fact Tables

russell80 — Mon, 13 Dec 2021 11:19:10 GMT

Thanks amitchandak . I gave that a go and it works for each row in the result, but the total is not right. I think for the total, you don't have the filter context to be able to sum the budgets for each project, so all the budgets are summed which comes back as a true condition for the "> 0" filter.

Re: Measure to Filter and Aggregate Across 2 Fact Tables

amitchandak — Mon, 13 Dec 2021 11:34:53 GMT

russell80 , These was small mistake, home you have forced row context using values and sumx

example or return

sumx(filter(Values(Project[project]), _bud >0),_inv)

sumx(summarize(Project,Project[project],"_1",_inv, "_2" _bud), if[_2]>0 ,[_1], blank() )

Re: Measure to Filter and Aggregate Across 2 Fact Tables

russell80 — Mon, 13 Dec 2021 11:59:22 GMT

Thanks amitchandak . I tried both options, see below but I still don't get the correct total

Re: Measure to Filter and Aggregate Across 2 Fact Tables

smpa01 — Mon, 13 Dec 2021 15:47:58 GMT

russell80 can you try this

Measure = VAR _filterTbl = SUMMARIZE ( FILTER ( Budgets, CALCULATE ( SUM ( Budgets[Value] ), ALLEXCEPT ( Budgets, Budgets[Name] ) ) > 0 ), Budgets[Name] ) RETURN CALCULATE ( SUM ( Invoices[Value] ), TREATAS ( _filterTbl, Invoices[Name] ) )