The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Dear Sir,
My query is to defined retailer class ( updated Retailer Class in trail mail) basis on the latest month of retailer transaction in power BI.
Please help to resolve this issue.
Retailer UID | Retailer Class | Month | Value | Updated Retailer Class | Remarks |
# | B | 01-06-2022 | 0.04771 | E | Based on latest month(Ex. 01-07-2022) |
# | E | 01-05-2022 | 0.03347 | E | Based on latest month(Ex. 01-07-2022) |
# | E | 01-06-2022 | 0.01668 | E | Based on latest month(Ex. 01-07-2022) |
# | E | 01-07-2022 | 0.00902 | E | Based on latest month(Ex. 01-07-2022) |
AP-0000001 | C | 01-05-2022 | 0.00229 | D | Based on latest month(Ex. 01-08-2022) |
AP-0000001 | D | 01-06-2022 | 0.00229 | D | Based on latest month(Ex. 01-08-2022) |
AP-0000001 | D | 01-08-2022 | 0.0048 | D | Based on latest month(Ex. 01-08-2022) |
AP-0000003 | C | 01-05-2022 | 0.00575 | D |
|
AP-0000003 | D | 01-06-2022 | 0.00574 | D |
|
AP-0000004 | C | 01-05-2022 | 0.0062 | B |
|
AP-0000004 | B | 01-06-2022 | 0.0056 | B |
|
AP-0000005 | D | 01-05-2022 | 0.00229 | A |
|
AP-0000005 | D | 01-06-2022 | 0.00699 | A |
|
AP-0000005 | A | 01-07-2022 | 0.00581 | A |
|
AP-0000006 | C | 01-05-2022 | 0.01172 | D |
|
AP-0000006 | D | 01-06-2022 | 0.00319 | D |
|
Solved! Go to Solution.
Hi @Anonymous ,
Please try this code to create a calculated column.
Updated Retailer Class Remarks =
VAR _LAST_MONTH =
CALCULATE (
MAX ( 'Table'[Month] ),
ALLEXCEPT ( 'Table', 'Table'[Retailer UID] )
)
RETURN
CALCULATE (
MAX ( 'Table'[Retailer Class] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Retailer UID] ),
'Table'[Month] = _LAST_MONTH
)
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thanks
Hi @Anonymous ,
Please try this code to create a calculated column.
Updated Retailer Class Remarks =
VAR _LAST_MONTH =
CALCULATE (
MAX ( 'Table'[Month] ),
ALLEXCEPT ( 'Table', 'Table'[Retailer UID] )
)
RETURN
CALCULATE (
MAX ( 'Table'[Retailer Class] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Retailer UID] ),
'Table'[Month] = _LAST_MONTH
)
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hi,
when we use this in power pivot data modle it's showing error
how can we use in power pivot data model
Error is :
The function MAX takes an argument that evaluates to numbers or dates and cannot work with values of type String.
User | Count |
---|---|
25 | |
10 | |
8 | |
7 | |
6 |
User | Count |
---|---|
32 | |
12 | |
10 | |
10 | |
9 |