Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Good Day,
So I have a dataset that is looking at inventory metrics at multiple plants. For example, I have determined where I have a plant that is the Primary consumer of a particular material at one plant and therefore in an effort to maximize material consumption, other plants could transfer their like material to them if they are not meeting certain criteria. In the example below, I would like help solving for the "Transfer To" Column by particular material filtered. So that the other plants would know whom to send their materials to get consumed and reduce overall inventory. All of the fields shown are available and I would like a DAX solution to help on this.
Thanks in advance for your help!
Plant | Material | Rank Consumption | Rank Inv Turns | Transfer | Primary | Transfer To |
A | 123 | 1 | 1 | NA | Primary | NA |
B | 123 | 4 | 3 | Transfer | Secondary | A |
C | 123 | 2 | 4 | Transfer | Secondary | A |
D | 123 | 3 | 2 | Transfer | Secondary | A |
A | 456 | 2 | 3 | Transfer | Secondary | B |
B | 456 | 1 | 1 | NA | Primary | NA |
C | 456 | 4 | 2 | Transfer | Secondary | B |
D | 456 | 3 | 4 | Transfer | Secondary | B |
Solved! Go to Solution.
Hi @heiligbd ,
Not sure if you want a calculated column or a measure but here is the values for both:
Calculated column:
TransferTo =
IF (
'Table'[Transfer] <> "NA",
MAXX (
TOPN (
1,
FILTER ( 'Table', 'Table'[Material] = EARLIER ( 'Table'[Material] ) ),
'Table'[Rank Inv Turns], ASC
),
'Table'[Plant]
),
'Table'[Transfer]
)
Measure:
TransferTo _ =
IF(SELECTEDVALUE('Table'[Transfer]) <> "NA",MAXX (
TOPN (
1,
FILTER ( ALLSELECTED('Table'[Material], 'Table'[Rank Inv Turns],'Table'[Plant]), 'Table'[Material] = MAX ( 'Table'[Material] ) ),
'Table'[Rank Inv Turns], ASC
),
'Table'[Plant]
), SELECTEDVALUE('Table'[Transfer]))
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsAwesome! Thanks for the help!
Hi @heiligbd ,
Not sure if you want a calculated column or a measure but here is the values for both:
Calculated column:
TransferTo =
IF (
'Table'[Transfer] <> "NA",
MAXX (
TOPN (
1,
FILTER ( 'Table', 'Table'[Material] = EARLIER ( 'Table'[Material] ) ),
'Table'[Rank Inv Turns], ASC
),
'Table'[Plant]
),
'Table'[Transfer]
)
Measure:
TransferTo _ =
IF(SELECTEDVALUE('Table'[Transfer]) <> "NA",MAXX (
TOPN (
1,
FILTER ( ALLSELECTED('Table'[Material], 'Table'[Rank Inv Turns],'Table'[Plant]), 'Table'[Material] = MAX ( 'Table'[Material] ) ),
'Table'[Rank Inv Turns], ASC
),
'Table'[Plant]
), SELECTEDVALUE('Table'[Transfer]))
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
79 | |
74 | |
56 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |