cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
crobinson661
Frequent Visitor

DAX responsive to selections in card, but not filtering in charts

Hello - 

This is probably a simple fix, but I am very new to DAX and cannot figure out the issue. 

I have a Measure [Transaction Submitted] that I am using in charts that divide values into Departments. [Transaction Submitted] is also represented in a card as well that needs to be responsive to Department selection in the chart, which I have accomplished with the help of the community. (Many thanks to @v-cgao-msft ) Here is that DAX:

 

 

Transaction Submitted =
CALCULATE (
    SUM ( 'Transaction List'[Budget] ),
    FILTER (
        ALLSELECTED ( 'Transaction List' ),
        OR ( 
            'Transaction List'[State] = "Ongoing"
            'Transaction List'[State] = "Complete"
    )
            && ( 'Transaction List'[Spent] = 0
            && 'Transaction List'[Processing] = 0
            && 'Transaction List'[Pre-Processing] = 0 )
    )
)

 

 

I am also referencing the [Transaction Submitted] above in another Measure [Aggregate Spend] that just SUM's two Measures [Transaction Submitted] and [Completed Transactions] in the charts on the page. Ex: 

 

 

Aggregate Spend = 'Transaction List'[Transaction Submitted] + 'Transaction List'[Completed Transactions]

 

 

The problem is: for each Department, the value of [Aggregate Spend] above includes [Transaction Submitted]'s whole value - not JUST the value for that Department. So, if the total value of [Transaction Submitted] for all Departments is $100k, on my charts where it is divided into Departments, EACH Department shows $100k when it should show only that Departments portion of [Transaction Submitted]  

I know the problem is not the OTHER measure [Completed Transactions] because when I remove [Transaction Submitted] from [Aggregate Spend], the value is properly divided by Department in the charts. 

 

I'm thinking I'm missing something in the original DAX that would allow the total to be divided by Department in the charts, likely because my initial inquiry did not include this context. 

 

Thank you in advance for any support!

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

You may want to take it easy on the filter stuff. Here is an alternative option

Transaction Submitted =
CALCULATE (
    SUM ( 'Transaction List'[Budget] ),
    ALLSELECTED ( 'Transaction List' ),
    'Transaction List'[State] IN { "Ongoing", "Complete"},
    'Transaction List'[Spent] = 0,
    'Transaction List'[Processing] = 0,
    'Transaction List'[Pre-Processing] = 0    
)

 

As for your question - Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

You may want to take it easy on the filter stuff. Here is an alternative option

Transaction Submitted =
CALCULATE (
    SUM ( 'Transaction List'[Budget] ),
    ALLSELECTED ( 'Transaction List' ),
    'Transaction List'[State] IN { "Ongoing", "Complete"},
    'Transaction List'[Spent] = 0,
    'Transaction List'[Processing] = 0,
    'Transaction List'[Pre-Processing] = 0    
)

 

As for your question - Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.