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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
v-rongtiep-msft
Community Support
Community Support

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 )
v-rongtiep-msft
Community Support
Community Support

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors