Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Laocsulak
Helper I
Helper I

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:

Laocsulak_0-1675743508147.png

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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())

vpollymsft_0-1675825287166.png

 

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.

View solution in original post

4 REPLIES 4
tamerj1
Super User
Super User

Hi @Laocsulak 
Please check if this is what you;re looking for.

1.png

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 )
Anonymous
Not applicable

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())

vpollymsft_0-1675825287166.png

 

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.

Laocsulak
Helper I
Helper I

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.

tamerj1
Super User
Super User

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.