Forum Discussion
Create table filtered with several considerations
Hello,
I need help creating a table where I have transactions paid in installments where the column status has the next characteristics:
Status = COMPLETED -> installment paid
Status = DUE -> installment overdue
Status = PENDING -> installment not paid, but not overdue
I need to create a table of users with the following characteristics:
- Users that have the last installment completed in the current or previous month (in this example in Feb-23 or Jan-23)
- Users that don't have due installments
- Users that don't have a new purchase in the current or previous month, that's to say, they don't have a first installment in the current or previous month
I provide the following dummy example to solve:
The column installment shows if it is the first installment (1), the second installment (2) or the third installment (3). There are purchases made in 2 and 3 installments.
In this example, the table only should contain users 1 and 2:
- User 1 was considered because he has the penultimate installment paid in January, doesn't have due installments, and doesn't have new purchases in January or February
- User 2 was considered because he has the last installment paid in January, doesn't have due installments, and doesn't have new purchases in January or February
- User 3 was not considered because he had an overdue installment
- User 4 was not considered because he had a new purchase in January
Files:
https://drive.google.com/drive/folders/10HO6y8kAdtun4gOUWEOPNOthsc-ktOaW?usp=share_link
Thanks in advance!
Hi Laocsulak ,
I have created a simple sample, please refer to my pbix file to see if it helps you.
Create 2 measures.
_DUE = IF(MAX('Table 1'[status])="DUE",0,1)Measure = var _thismonth=TODAY() var _lastmonth=EDATE(TODAY(),-1) var _minbuy=CALCULATE(MIN('Table 1'[created_date]),FILTER(ALL('Table 1'),'Table 1'[user]=SELECTEDVALUE('Table 1'[user])&&'Table 1'[installment]=MINX(ALLSELECTED('Table 1'),'Table 1'[installment]))) VAR _DUE=MINX(FILTER(ALLSELECTED('Table 1'),'Table 1'[user]=SELECTEDVALUE('Table 1'[user])),[_DUE]) VAR _NEWBUY=IF(_minbuy>=_lastmonth&&_minbuy<=_thismonth,BLANK(),1) RETURN IF(_DUE=0,BLANK(),IF(_NEWBUY=0,BLANK(),1))Then you can create a new table.
Table = FILTER(SUMMARIZE('Table 1','Table 1'[user],'Table 1'[order_id],'Table 1'[installment],"aa",[Measure]),[Measure]<>BLANK())How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ PollyIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi Laocsulak ,
I have created a simple sample, please refer to my pbix file to see if it helps you.
Create 2 measures.
_DUE = IF(MAX('Table 1'[status])="DUE",0,1)Measure = var _thismonth=TODAY() var _lastmonth=EDATE(TODAY(),-1) var _minbuy=CALCULATE(MIN('Table 1'[created_date]),FILTER(ALL('Table 1'),'Table 1'[user]=SELECTEDVALUE('Table 1'[user])&&'Table 1'[installment]=MINX(ALLSELECTED('Table 1'),'Table 1'[installment]))) VAR _DUE=MINX(FILTER(ALLSELECTED('Table 1'),'Table 1'[user]=SELECTEDVALUE('Table 1'[user])),[_DUE]) VAR _NEWBUY=IF(_minbuy>=_lastmonth&&_minbuy<=_thismonth,BLANK(),1) RETURN IF(_DUE=0,BLANK(),IF(_NEWBUY=0,BLANK(),1))Then you can create a new table.
Table = FILTER(SUMMARIZE('Table 1','Table 1'[user],'Table 1'[order_id],'Table 1'[installment],"aa",[Measure]),[Measure]<>BLANK())How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ PollyIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Laocsulak
Please check if this is what you;re looking for.FilterMeasure = VAR CurrentTable = transactions VAR Condition1 = NOT ISEMPTY ( FILTER ( CurrentTable, transactions[status] = "COMPLETED" ) ) VAR Condition2 = ISEMPTY ( FILTER ( CurrentTable, transactions[status] = "DUE" ) ) VAR NumOfOrders = COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( CurrentTable, "@Order", transactions[order_id] ) ) ) VAR Condition3 = NumOfOrders < 2 RETURN INT ( Condition1 && Condition2 && Condition3 )Every user could have many order_id. In this example, users 4, 2, and 1 have only one order_id, and user 3 has two order_id.
