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
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors