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

Help finding first product purchased by customer where customer has purchased multiple products

Hi,


I have a table:

 

CUST A | Product A| 1/1/19 | Tire Dept | Tires

CUST A | Product B| 1/1/20 | Acme Dept | Bells

CUST A | Product C| 1/1/18 | Tire dept. | Tires

 

I tried a Calculate by Min(product, filter(earlier(date)), Filter (Tires))

 

But I'm not sure how to write this. 

 

I need to find the first product sold by Customer in "Tires" by Tire Dept for any Product.

 

In this case it should be Customer A, Product C, for Tires in Tire dept. 

 

Can anyone help. Dax is not like Excel.

 

1 ACCEPTED SOLUTION

Hi, @Anonymous 

 

You may try the following measure.

Sales Price =
var _cust = SELECTEDVALUE('MF Data 6_25'[Account Name])
var _tor = SELECTEDVALUE('MF Data 6_25'[Sales Price (converted)])
 
var _date =
CALCULATE(
MIN('MF Data 6_25'[Created Date].[Date]),
FILTER(
ALL('MF Data 6_25'),
'MF Data 6_25'[Account Name] = _cust&&
'MF Data 6_25'[Product LOBs] = "Career"&&
'MF Data 6_25'[ColSales] < 50000
 
)
)
var _product =
CONCATENATEX(
FILTER(
ALL('MF Data 6_25'),
'MF Data 6_25'[Account Name] = _cust&&
'MF Data 6_25'[Product LOBs] = "Career"&&
'MF Data 6_25'[Created Date].[Date] = _date
),
[Product Name]
)
return
_TOR

 

Best Regards

Allan

 

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

6 REPLIES 6
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

d1.png

 

You may create a measure as below.

Result = 
var _cust = SELECTEDVALUE('Table'[CUST])
var _date = 
CALCULATE(
            MIN('Table'[Date]),
            FILTER(
                ALL('Table'),
                'Table'[CUST] = _cust&&
                'Table'[Dept] = "Tire Dept"
            )
        )
var _product = 
CONCATENATEX(
    FILTER(
        ALL('Table'),
        'Table'[CUST] = _cust&&
        'Table'[Dept] = "Tire Dept"&&
        'Table'[Date] = _date
    ),
    [Product]
)
return
_cust&"|"&_product&"|"&_date&"|Tire Dept|Tires"

 

Result:

d2.png

 

Best Regards

Allan

 

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

Anonymous
Not applicable

Hi @v-alq-msft Allan or someone:

 

How can I filter out another column based on values?

 

I would like to take out values less than 50,000.

 

So if we had a product that sold for less than 50,000 and it was the earliest/first product sold, then the formula would skip it and move to the next product to see if it was greater than 50,000.

 

Sales Price =
var _cust = SELECTEDVALUE('MF Data 6_25'[Account Name])
var _tor = SELECTEDVALUE('MF Data 6_25'[Sales Price (converted)])
----> Filter(Value(TableColSales, ColSales < 50,000))? 
 
var _date =
CALCULATE(
MIN('MF Data 6_25'[Created Date].[Date]),
FILTER(
ALL('MF Data 6_25'),
'MF Data 6_25'[Account Name] = _cust&&
'MF Data 6_25'[Product LOBs] = "Career"
 
 
)
)
var _product =
CONCATENATEX(
FILTER(
ALL('MF Data 6_25'),
'MF Data 6_25'[Account Name] = _cust&&
'MF Data 6_25'[Product LOBs] = "Career"&&
'MF Data 6_25'[Created Date].[Date] = _date
 
),
[Product Name]
)
return
_TOR

Hi, @Anonymous 

 

You may try the following measure.

Sales Price =
var _cust = SELECTEDVALUE('MF Data 6_25'[Account Name])
var _tor = SELECTEDVALUE('MF Data 6_25'[Sales Price (converted)])
 
var _date =
CALCULATE(
MIN('MF Data 6_25'[Created Date].[Date]),
FILTER(
ALL('MF Data 6_25'),
'MF Data 6_25'[Account Name] = _cust&&
'MF Data 6_25'[Product LOBs] = "Career"&&
'MF Data 6_25'[ColSales] < 50000
 
)
)
var _product =
CONCATENATEX(
FILTER(
ALL('MF Data 6_25'),
'MF Data 6_25'[Account Name] = _cust&&
'MF Data 6_25'[Product LOBs] = "Career"&&
'MF Data 6_25'[Created Date].[Date] = _date
),
[Product Name]
)
return
_TOR

 

Best Regards

Allan

 

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

Anonymous
Not applicable

@v-alq-msft - This worked great! I now get how to use filters within these equations!

amitchandak
Super User
Super User

@Anonymous , Try like

calculate(firstnonblankvalue(Table[Date],Min(Table[product])), Table[Type]="Tires")
Anonymous
Not applicable

Hi @amitchandak 

 

Why would we use nonblankvalue? All values would be answered in this case.

 

I clarified my question above, this also has to find the Customer at the end of the day.

 

So the answer would be: CUST A | Product C| 1/1/18 | Tire dept. | Tires

 

keep in mind, there may be CUST B, or C or others in this list.

 

Thanks,

 

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.