Power BI Conditional Base filter calculation

[619SK]

Im not able to find the neasur to calculate below condition in measure , i have updated sample

Final expected data to be on bi

[jennratten]

@619SK Please review the solution posted and click Accept as Solution if it answered your question.  Thanks!

[619SK]

Thanks for query

Suppose my count result is 0-15 =66

my query is  = if [Age_c] ="0-15" and [Count] >=30 then 95, if [Age_c] = "0-15" and [Count] =>50 then 90 else 100

Here ideally expected result for 66 count is 90 but i'm getting result of 95 point.

I'm getting first query as result.

[jennratten · Accepted solution]

• It's because of the order in which they are listed. 66 is greater than 30 so a match is found and no further rules will be evaluated for that value.  They should be evaluated in descending order.

= if [Age_c] ="0-15" and [Count] >=50 then 90 else if [Age_c] = "0-15" and [Count] >= 30 then 95 else 100

[jennratten]

Hello - this is how you can accomplish it in Power Query.  Note, there are a couple of gaps in your conditions - an upper limit of defects is missing for conditions 2 and 3 and a condition is missing for the 15-30 age category.  In the solution provided, I have included the result of these as 50%.  Please change that value to whatever it should be for your requirement.  For the 15-30 category, the conditions just need to be added in the same manner.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRW0lEyMgASBrqGpkDKvyA1TylWByhjAuRZGAIJMwNdSwMUKZBCU5CQsYGuGaqUGZBnaAkiTHWNQVLOOfnFqRA5czxyFnjkLHFaB3Y5DimQ0zE8FgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Defect ID" = _t, Age = _t, #"Age Category" = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Defect ID", Int64.Type}, {"Age", Int64.Type}, {"Age Category", type text}}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Changed Type", "Age Category Max", each Number.FromText ( Text.AfterDelimiter([Age Category], "-") ), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Text After Delimiter", {"Age Category", "Age Category Max"}, {{"Total Defects", each List.Count([Defect ID]), type nullable number}, {"Open Defects", each List.Count ( List.Select ( [Status], each _ = "Open" ) ), type nullable number},{"Data", each _, type table [Defect ID=nullable number, Age=nullable number, Age Category=nullable text]}}),
    #"Added Custom" = Table.AddColumn(
        #"Grouped Rows", "Points", each 
            if [Total Defects] = 0 then 100
            else if [Age Category] = "0-15" and [Open Defects] >= 30 then 80 
            else if [Age Category] = "0-15" then 100
            else if [Age Category] = "30-60" and [Total Defects] >= 40 then 50 
            else if [Age Category] = "30-60" and [Total Defects] >= 30 then 80 
            else if [Age Category] = "30-60" and [Total Defects] >= 20 then 90 
            else if [Age Category] = "30-60" then 100 
            else if [Age Category Max] >= 90 and [Total Defects] >= 45 then 50
            else if [Age Category Max] >= 90 and [Total Defects] >= 40 then 80
            else if [Age Category Max] >= 90 and [Total Defects] >= 30 then 90
            else if [Age Category Max] >= 90 then 100
            else null, type nullable number )
in
    #"Added Custom"

Based on the sample data provided, the only results are either 100 or null.  Please apply this to your full data table and let me know if the result is not as expected.  I will also be glad to help add the additional criteria to accommodate the gaps if you will provide the necessary info.