Finding the minimum value of possible values and assign corresponding value

[sharankv]

Hi all,

I've data like below where each Problem has multiple Incident Numbers and each Incident Number is assigned to a category. The output should show the value of a category of a minimum incident(considering the numerical values only in incident) following the priority order (With Impact, Without Impact, Not Available).

In simpler terms, if a Problem has all types of category, the output should be the Category with highest priority order assigned next to its minimum incident i.e., With Impact.

Ex: PBI0002 has With Impact and Without Impact, as the With Impact is of highest priority order in Category, the Category is mapped to the incident INC10003 only but not to INC10005 (as INC10003 is minimum from that of INC10005)

PS: I want to draw this information using a Measure in a straight table.

Appreciate your responses and thanks in advance

With Love

Sharan

[Clement15]

Hello,

This may not be very optimized but it meets your needs:

Data:

Load

*,

KeepChar("Incident Number",'123456789')as Num_Incident

;

LOAD

    S.No.,

    "Problem ID",

    "Incident Number",

    Category

FROM [lib://DataFiles/Sample Data.xlsx]

(ooxml, embedded labels, table is Sheet1);




NoConcatenate




Temp:

Load

    [Problem ID],

    min(Num_Incident) as Num_Incident

Resident Data

group by [Problem ID];




left join(Temp)




Load

    [Problem ID],

    Num_Incident,

 Category

Resident Data;







left join(Data)

Load

    [Problem ID],

    Num_Incident,

 Category as Outpout

Resident Temp;




Drop table Temp;

[sharankv]

Hi,

Thanks for your reply. I'm looking for a solution as an expression in a measure in the table. The load script statement solution doesn't fit for my use case.

Appreciate your response. Thanks 

[marcus_sommer]

I would probably use a dual() value for the category within the script - maybe something:

dual('with impact', 3)
dual('without impact', 2)
dual('not available', 1)

and then the highest value could be simply queried with max() against the max(total) and/or within aggr() which will depend on the specific data- and object-design.