Forum Discussion
Filter out clients that have a value
Hi All,
I am currently trying to create a report where it shows the clients that have not had returns.
I would preferable like to do it through a DAX measure so that I can use date filters and other filters at a later stage.
I have three tables I am working with, I have simplified them as much as possible just to reflect the data model I am working with.
The tables are as follows:
Customers
| Customer_ID | Customer_Name |
| 1 | John |
| 2 | Bob |
Fact_Tansactions
| Transaction_No | Amount | Customer_ID |
| 1 | 100 | 1 |
| 2 | 100 | 1 |
| 3 | 200 | 2 |
| 4 | 50 | 2 |
| 5 | 200 | 2 |
Transaction_desc
| Transaction_No | Transaction_Type | Transaction_Date |
| 1 | Purchase | 10/10/2021 |
| 2 | Purchase | 12/10/2021 |
| 3 | Purchase | 18/10/2021 |
| 4 | Purchase | 20/10/2021 |
| 5 | Return | 28/10/2021 |
The Customer and Transaction_desc tables are moth linked to the Fact_Transaction table.
The output I would like to create is:
| Customer_Name | Amount |
| John | 200 |
Where the measure excludes all of the transactions when calculating the amount for Customer_ID 2 because of the turn they have on the Transcation_desc table.
Any help will be much appreciated, thank you.
Hi Anonymous ,
According to this——shows the clients that have not had returns
You could firstly create a flag measure:
Flag = VAR _AllTranType = ADDCOLUMNS ( SUMMARIZE ( 'Fact_Tansactions', [Customer_ID], [Transaction_No] ), "Type", LOOKUPVALUE ( 'Transaction_desc'[Transaction_Type], [Transaction_No], 'Fact_Tansactions'[Transaction_No] ) ) VAR _t = SUMMARIZE ( FILTER ( _AllTranType, [Customer_ID] = MAX ( 'Customers'[Customer_ID] ) ), [Type] ) RETURN IF ( "Return" IN _t, 1, 0 )And apply it to visual-level filter , set as "is 1":
Or if you want to show all customers on a Card visual, please try:
All Customers did not return = CONCATENATEX(FILTER('Customers',[Flag]=1),[Customer_Name],",")Final Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- amitchandak
Super User
Anonymous , Not very clear, is customer 2 not appearing because of return.
Seems like simple addition unless we use logic of the return
if it is 1-1 or then we can get their type
in Fact_Tansactions
maxx(filter(Transaction_desc, Transaction_desc[Transaction_No] = Fact_Tansactions[Transaction_No] ) ,Transaction_desc[Transaction_Type] )
if they are 1=1, then better to merge those
Hi amitchandak ,
Thank you for your help, and sorry if I wasn't completely clear.
To answer your first question: yes I don't want customer 2 showing on the table due to there being a refund for them.
Hi Anonymous ,
According to this——shows the clients that have not had returns
You could firstly create a flag measure:
Flag = VAR _AllTranType = ADDCOLUMNS ( SUMMARIZE ( 'Fact_Tansactions', [Customer_ID], [Transaction_No] ), "Type", LOOKUPVALUE ( 'Transaction_desc'[Transaction_Type], [Transaction_No], 'Fact_Tansactions'[Transaction_No] ) ) VAR _t = SUMMARIZE ( FILTER ( _AllTranType, [Customer_ID] = MAX ( 'Customers'[Customer_ID] ) ), [Type] ) RETURN IF ( "Return" IN _t, 1, 0 )And apply it to visual-level filter , set as "is 1":
Or if you want to show all customers on a Card visual, please try:
All Customers did not return = CONCATENATEX(FILTER('Customers',[Flag]=1),[Customer_Name],",")Final Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
