topic Re: Create table filtered with several considerations in DAX Commands and Tips

Create table filtered with several considerations

Laocsulak — Tue, 07 Feb 2023 04:20:39 GMT

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!

Re: Create table filtered with several considerations

tamerj1 — Tue, 07 Feb 2023 06:40:12 GMT

Hi Laocsulak
User 4 has only one order id?! Please clarify this point further.

Re: Create table filtered with several considerations

Laocsulak — Tue, 07 Feb 2023 16:36:21 GMT

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.

Re: Create table filtered with several considerations

Anonymous — Wed, 08 Feb 2023 03:02:06 GMT

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 _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Create table filtered with several considerations

tamerj1 — Wed, 08 Feb 2023 07:36:03 GMT

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 )