Only get filtered results from connected table

[ronnie_roberts]

Good morning,

I've been struggling with this for a few days.  I have 2 tables (Budgets & Data), a guage, and a slicer.  I'm trying to get the guage to show the correct target budget no matter Category (or mix of categories) is selected in the slicer.

Below is the code I have, but both NoFilter and HasFilter keep returning the same value or those Big Projects with mutiple subprojects get over represented.  I've tried various GROUPBY, SUMMARIZE, etc, and I just cannot get this to work.  What am I doing wrong?  Shouldn't VALUES only return the Categories currently in the filter?

_Target Budget = 
VAR FilterCheck = ISFILTERED('Data'[Category])
VAR NoFilter = SUMX('Budgets', [Budget])
VAR HasFilter = 
    ADDCOLUMNS(
        VALUES('Budgets'[Category]),
        "@Budget", SUMX('Budgets', [Budget])
    )
VAR Result = 
    IF(
        FilterCheck,
        SUMX(HasFilter, [@Budget]),
        NoFilter
    )
RETURN Result

Budgets:

PK_ID	Category	Budget
BP1	Big Project 1	1,000,000
BP2	Big Project 2	2,000,000
BP3	Big Project 3	5,000,000
BP4	Big Project 4	2,000,000

Data:

PK_ID	FK_ID	Name	Cost
SP1	BP1	Subproject 1	500,000
SP2	BP2	Subproject 2.1	1,500,000
SP3	BP2	Subproject 2.2	200,000
SP4	BP3	Subproject 3	4,300,000
SP5	BP4	Subproject 4	2,500,000

Edit 2/1/2024: Edited the pasted formula.  I did a lot of simplifying my code for understanding and screwed up which table a column was tied to.

[ronnie_roberts]

Thank you for the feedback!  I did a ton of simplifying creating this example and when I set things up as you suggested, yeah SUMX works just fine.  Obviously one of the other things going on in my report is causing the issue.  I'll keep searching.

[gmsamborn]

@ronnie_roberts 

Does this help?  I added CALCULATE around your SUMX when defining _HasFilter.

_Target Budget 2 = 
VAR _FilterCheck = ISFILTERED('Budgets'[Category])
VAR _NoFilter = SUMX('Budgets', [Budget])
VAR _HasFilter = 
    ADDCOLUMNS(
        VALUES( 'Budgets'[Category] ),
        "@Budget", CALCULATE( SUMX('Budgets', [Budget])  )
    )
VAR _Result = 
    IF(
        _FilterCheck,
        SUMX( 
            _HasFilter, 
            [@Budget]
        ),
        _NoFilter
    )
RETURN 
    _Result

Can you explain why you wrote your measure that way?  As far as I can tell, [My Budget] mentioned above seems to work.

Only get filtered results-from connected table - test.pbix

[gmsamborn · Accepted solution]

Hi @ronnie_roberts 

I'm not sure what your measure would have over something like this.

My Budget = SUM( 'Budgets'[Budget] )

Take a look at my comparison.

Only get filtered results-from connected table.pbix

[gmsamborn]

Hi @ronnie_roberts 

In your measure you refer to 'Data'[Category] but in your tables you say that [Category] is a column of Budget, not Data.

Let me know if this helps.