Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
Id like PowerBI to give me the nth occurrence of a value, in DAX or M language.
For each SKU that can appear several times, I have several potential suppliers. Id like to display the nth vendor per row as follows :
Date | SKU | Vendor | 1st vendor | 2nd vendor |
2021-08-06 | Apple | Franck | Franck | Franck |
2021-09-07 | Strawberry | Mary | Mary | Franck |
2021-09-20 | Strawberry | Franck | Mary | Franck |
2021-09-21 | Pineapple | Paul | Paul | null |
2021-11-10 | Apple | Mary | Franck | Franck |
2021-11-15 | Cherry | Paul | Paul | null |
Thanks for your help
Solved! Go to Solution.
Hi @Marion , the solutions are to be utlized as measures and not calculated columns. I f you refer to the attached pbix, you will see.
@Marion you can utilize the following measures
1stVendor =
CALCULATE (
MAXX (
FILTER (
ADDCOLUMNS (
'Table',
"rank",
RANKX (
FILTER ( 'Table', EARLIER ( 'Table'[SKU] ) = 'Table'[SKU] ),
CALCULATE ( MAX ( 'Table'[Date] ) ),
,
DESC
)
),
[rank] = 1
),
[Vendor]
),
ALLEXCEPT ( 'Table', 'Table'[SKU] )
)
2ndVendor =
CALCULATE (
MAXX (
FILTER (
ADDCOLUMNS (
'Table',
"rank",
RANKX (
FILTER ( 'Table', EARLIER ( 'Table'[SKU] ) = 'Table'[SKU] ),
CALCULATE ( MAX ( 'Table'[Date] ) ),
,
DESC
)
),
[rank] = 2
),
[Vendor]
),
ALLEXCEPT ( 'Table', 'Table'[SKU] )
)
Thank you smpa01 ! Looks like it is the right formula.
However I have the following error message when I copy-paste the formula for the 1st vendor in a new column:
A circular dependency was detected: Table[Column]
do you know how to fix it?
Thanks a lot
Hi @Marion , the solutions are to be utlized as measures and not calculated columns. I f you refer to the attached pbix, you will see.
It works thank you !
@Marion you will get nth occurnece by SKU with the following measure
Measure =
CALCULATE (
MAX ( 'Table'[1st vendor] ),
FILTER (
'Table',
'Table'[Date]
= CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[SKU] ) )
)
)
Hi smpa01 thanks for your answer.
Columns "1st vendor" and "2nd vendor" are the outputs Id like PowerBI to generate.
The initial database only contains colums "date", "sku" and "vendor".
Sorry I made a mistake in my table :
Date | SKU | Vendor | 1st vendor | 2nd vendor |
2021-08-06 | Apple | Franck | Franck | Mary |
2021-09-07 | Strawberry | Mary | Mary | Franck |
2021-09-20 | Strawberry | Franck | Mary | Franck |
2021-09-21 | Pineapple | Paul | Paul | null |
2021-11-10 | Apple | Mary | Franck | Mary |
2021-11-15 | Cherry | Paul | Paul | null |