Forum Discussion
Filter based list to use for calculation
Hi All, I have scenario that I need to have sum(discount_amt) only for customers which has certain filter applied on it. I have query which gives me data customer wise discount applied., Now 1. I h...
Hi Anonymous,
You can try to use below measure to use the filtered result as the parameter to filter on original table:
Multi-filter result = VAR filtred = CALCULATETABLE ( VALUES ( 'Sample'[Cust Id] ), ALLSELECTED ( 'Sample' ) ) RETURN SUMX ( FILTER ( ALL ( 'Sample' ), [Cust Id] IN filtred ), [Disc Amount] )Regards,
Xiaoxin Sheng
| Cust Id | Sale Id | Success | Yesterday | 5in90 | Disc Amount |
| 123 | 111 | Y | N | N | 1000 |
| 234 | 222 | Y | Y | Y | 2000 |
| 123 | 333 | Y | Y | Y | 3000 |
| 456 | 444 | Y | Y | N | 4000 |
| 789 | 555 | N | Y | N | 5000 |
For Ex in Above tBale if I apply filter as Success=Y,Yesterday=Y and 5in 90 =Y I get below customers
| Cust Id | Sale Id | Success | Yesterday | 5in90 | Disc Amount |
| 234 | 222 | Y | Y | Y | 2000 |
| 123 | 333 | Y | Y | Y | 3000 |
Then I need to clear all filters goto all sale IDs for Cust ID 234,123 which are below
| Cust Id | Sale Id | Success | Yesterday | 5in90 | Disc Amount |
| 123 | 111 | Y | N | N | 1000 |
| 234 | 222 | Y | Y | Y | 2000 |
| 123 | 333 | Y | Y | Y | 3000 |
and then do sum(discount_amount) which is $6000, I wanted to do this in DAX if possible.
Hi Anonymous,
You can try to use below measure to use the filtered result as the parameter to filter on original table:
Multi-filter result =
VAR filtred =
CALCULATETABLE ( VALUES ( 'Sample'[Cust Id] ), ALLSELECTED ( 'Sample' ) )
RETURN
SUMX ( FILTER ( ALL ( 'Sample' ), [Cust Id] IN filtred ), [Disc Amount] )
Regards,
Xiaoxin Sheng