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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.