Forum Discussion

ben_w's avatar
ben_w
Frequent Visitor
8 years ago

Lookup maximum value based on chosen period

  I've got a need to multiply [Budget Cost] by [Percentage of completion] to get the budget that is already allowed to be spent, we call it [Free budget]. The [Percentage of completion] is saved fo...
Need Help
v-sihou-msft's avatar
v-sihou-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

ben_w

 

You want to lookup [Percentage of completion]  based [Instance], [Project] [LineNr] and [UpdateId], but [UpdateId] should be the [UpdateId] under same [Instance], [Project] [LineNr]. Right?

 

In this scenario, you should calcualte the MAX [UpdateId] first, and within FILTER() function, the matched value in condition should be aggregated. So your measure should be like: 

 

Free budget =
VAR maxUpdateId =
    CALCULATE (
        MAX ( Fact_Progress_Status_Line[UpdateId] ),
        ALLEXCEPT (
            Fact_Progress_Status_Line,
            Fact_Progress_Status_Line[Instance],
            Fact_Progress_Status_Line[Project],
            Fact_Progress_Status_Line[LineNr]
        )
    )
RETURN
    SUMX (
        Fact_Sales_Line_Detail,
        [Budget Cost]
            * (
                CALCULATE (
                    VALUES ( Fact_Progress_Status_Line[Percentage of completion] ),
                    FILTER (
                        ALL ( Fact_Progress_Status_Line ),
                        Fact_Progress_Status_Line[Instance] = MAX ( Fact_Sales_Line_Detail[Instance] )
                            && Fact_Progress_Status_Line[Project] = MAX ( Fact_Sales_Line_Detail[Project] )
                            && Fact_Progress_Status_Line[LineNr] = MAX ( Fact_Sales_Line_Detail[LineNr] )
                            && Fact_Progress_Status_Line[UpdateId] = maxUpdateId
                    )
                )
            )
    )

Regards,

 

 

ben_w's avatar
ben_w
Frequent Visitor
8 years ago

v-sihou-msft

 

I've tried many variations on your code but the passing of the "parameters(filters)" never seems to go through. Even if i take away the allexcept to avoid issues there, nothing is returned.

 

It makes we wonder if getting a value dynamically from another table for every row with "parameters" of the evaluated row is even possible?