Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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.
Hi, @Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
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:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
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.
@v-alq-msft - This worked great! I now get how to use filters within these equations!
@Anonymous , Try like
calculate(firstnonblankvalue(Table[Date],Min(Table[product])), Table[Type]="Tires")
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,
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |