Searching a Column to Return Value

[caruso1058]

Hello I have a a Table with several iterations of part numbers.
If a part is still in development then I need to return the latest part number. 

If the part is no longer in Development then return "Production" 

Here is what my Data looks like:

Part Number	PN_Base	PN_Suffix	Count_of_PN_Bases
Q1234-900	Q1234	900	5
Q1234-901	Q1234	901	5
Q1234-902	Q1234	902	5
Q1234-903	Q1234	903	5
Q1234-001	Q1234	001	5
Q9876-900	Q9876	900	3
Q9876-901	Q9876	901	3
Q9876-001	Q9876	001	3
Q5678-900	Q5678	900	2
Q5678-901	Q5678	901	2

I would like to create a calculated column or two to determine the latest version of a Part and its current state such as this:

Part Number	PN_Base	PN_Suffix	Count_of_PN_Bases	Current_Version	State
Q1234-900	Q1234	900	5	FALSE	Development
Q1234-901	Q1234	901	5	FALSE	Development
Q1234-902	Q1234	902	5	FALSE	Development
Q1234-903	Q1234	903	5	FALSE	Development
Q1234-001	Q1234	001	5	TRUE	Production
Q9876-900	Q9876	900	3	FALSE	Development
Q9876-901	Q9876	901	3	FALSE	Development
Q9876-001	Q9876	001	3	TRUE	Production
Q5678-900	Q5678	900	2	FALSE	Development
Q5678-901	Q5678	901	2	TRUE	Development

[amitchandak · Accepted solution]

@caruso1058 , do you date column. Because all these columns will not lead to max mix etc.

as of now create index column

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

and try like

if([Index] = maxx(filter(table,[PN_Base]=earlier([PN_Base])),[index]),"Production", "Development")

The ideal would be a date. in place of index , as index it orde rin which got data in power bi

[mahoneypat · Accepted solution]

This is one way to do this in a calculated column expression. Note that you can choose to return any of the last 3 variables for different results (for example, the latest version, Dev vs Prod, or It's the last T or F).

Latest Version =
VAR thisversion = Parts[PN_Suffix]
VAR latestproduction =
    CALCULATE (
        MAX ( Parts[PN_Suffix] ),
        ALLEXCEPT ( Parts, Parts[PN_Base] ),
        FILTER ( ALL ( Parts[PN_Suffix] ), VALUE ( Parts[PN_Suffix] ) < 900 )
    )
VAR latestdev =
    CALCULATE (
        MAX ( Parts[PN_Suffix] ),
        ALLEXCEPT ( Parts, Parts[PN_Base] ),
        FILTER ( ALL ( Parts[PN_Suffix] ), VALUE ( Parts[PN_Suffix] ) > 900 )
    )
VAR latest =
    IF ( ISBLANK ( latestproduction ), latestdev, latestproduction )
VAR islatest = thisversion = latest
VAR prodordev =
    IF ( ISBLANK ( latestproduction ), "Development", "Production" )
RETURN
    latest

If this works for you, mark it as the solution. Praise is also appreciated. Please let me know if you don't.

Best regards

Pat

[caruso1058]

Wow @mahoneypat ,

This is beautiful and works perfectly. I can only hope that my skills are as a savy as this one day!

Thanks a bunch!

[amitchandak · Accepted solution]

@caruso1058 , do you date column. Because all these columns will not lead to max mix etc.

as of now create index column

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

and try like

if([Index] = maxx(filter(table,[PN_Base]=earlier([PN_Base])),[index]),"Production", "Development")

The ideal would be a date. in place of index , as index it orde rin which got data in power bi

[caruso1058]

Hello @amitchandak ,

I might be able to bring in the date values with a another query from the main data source.  If I am able to bring in the date value then would the Calculated Column look like this:

IF([DATE] = MAXX(FILTER(table,[PN_Base]=EARLIER([PN_Base])),[DATE]),"Production", "Development")  ?

Thank you for your help with this!