Calculated column based on condition depending on other columns

[Anonymous]

Suppose I have a simple CSV file like this:

Unit,CategoryA,CategoryB,CategoryC
U1,99,90,82
U2,92,100,56
U3,96,50,66
U4,85,33,99
U5,21,94,100

I would like to have a new calculated column, say, 'PassCount' that will reflect the total of 'pass' values of last two Category columns. Let's say my 'pass' value is 90. So, for example, for U1 one of the last 2 category values are >= 90, so my PassCount should be 1. For U2, only 1 as well. Essentially, my table with the calculated column I want should look like this:

Unit,CategoryA,CategoryB,CategoryC,PassCount,
U1,99,90,82,1
U2,92,100,56,1
U3,96,50,66,0
U4,85,33,99,1
U5,21,94,100,2

I can get the end result by creating a column like this.

PassCount = COUNTROWS(FILTER({Data[CategoryB],Data[CategoryC]},[Value]>=90))+0

Note: The +0 at the end so that COUNTROWS returns 0 instead of blank in case there's no rows matching the condition.

This is fine, but what I would like to ideally do is instead of specifying hardcoded [CategoryB] and [CategoryC] columns in the FILTER() function, is it possible the get the 'last two' Category columns? The reason is that every week I might have new columns, but I always want the last two categories. So is there a dynamic way for FILTER to filter the last two such rows instead of hardcoding them?

[amitchandak]

@Anonymous , You can create a column like this

Pass count =if([categoryA]>90,1,0)+if([categoryB]>90,1,0)+if([categoryC]>90,1,0)

measure = sumx(Table, if([categoryA]>90,1,0)+if([categoryB]>90,1,0)+if([categoryC]>90,1,0))

[Anonymous]

@amitchandak thanks, but as I pointed out in the post the question is not about creating the column. I'm wondering if there's a way to get the calculated column based on last two columns before it, without having to hardcode column names.

[edhans · Accepted solution]

---

@Anonymous wrote:

@amitchandak thanks, but as I pointed out in the post the question is not about creating the column. I'm wondering if there's a way to get the calculated column based on last two columns before it, without having to hardcode column names.

---

Not in DAX. You would have to do this in Power Query or normalize your data in Power Query before bringing into DAX. 
Here is what I did. This is not the answer you are looking for, but may get you down that path.

First, I normalized your data. So 

becomes:

The [Index] column will tell me what the last 2 columns are as I cannot rely on the column names, now the [Category] column to reliably tell me the most recent two based on whatever text string is in there.

M code for that is:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs3LLFHSUXJOLElNzy+qdERiOyGxnZVidYCKDYFClpYgwgBIWBhBRI1AAiDC0AAkbGoGETYGCZuBBECiZlBRE5BGUyBhbAwxDCwKEjACm24CNSc2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CategoryA", Int64.Type}, {"CategoryB", Int64.Type}, {"CategoryC", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Unit"}, "Category", "Amount"),
    #"Grouped Rows" = 
        Table.Group(
            #"Unpivoted Other Columns", 
            {"Unit"}, 
            {
                {"All Rows", each Table.AddIndexColumn(_, "Index", 1, 1), type table [Unit=nullable text, Category=text, Amount=Int64.Type, Index=Int64.Type]}
            }
        ),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Category", "Amount", "Index"}, {"Category", "Amount", "Index"})
in
    #"Expanded All Rows"

Then, I used this measure.

Measure = 
VAR varMaxIndex =
    CALCULATE(
        MAX( 'Table'[Index] ),
        ALLEXCEPT(
            'Table',
            'Table'[Unit]
        )
    )
VAR varMaxCategory =
    CALCULATE(
        MAX( 'Table'[Category] ),
        'Table'[Index] = varMaxIndex,
        ALLEXCEPT(
            'Table',
            'Table'[Unit]
        )
    )
VAR var2ndMaxCategory =
    CALCULATE(
        MAX( 'Table'[Category] ),
        'Table'[Index] = varMaxIndex - 1,
        ALLEXCEPT(
            'Table',
            'Table'[Unit]
        )
    )
VAR Result =
    CALCULATE(
        SUM( 'Table'[Amount] ),
        FILTER(
            ALLEXCEPT('Table', 'Table'[Unit]),
            'Table'[Category]
                IN {
                varMaxCategory,
                var2ndMaxCategory
            }
        )
    )
RETURN
    Result

This will always return the highest and 2nd highest [Index] per unit, and consequetly the last two categories.

You can see this in this table visual.

The value for U1 is 172, which is 90 + 82. U2 is 156, which is 100 + 56.

This measure works anywhere, so it could be a bar chart with none of the other values in it, like so:

Does that help? If not, provide some feedback so we can continue to assist.

[Anonymous]

Thanks for the detailed explanation. As you said this won't directly help my situation but I think I can get the basic idea. Looks like for most of the things that I want to do I need to pivot my tables, and I'm thinking of referencing my original data to create a pivoted table.

[edhans]

Sounds good @Anonymous 

There are time I have two copies of a table in the DAX model, one in an original view, and another in a normalized or different view. I'd rather model it twice than have a bunch of complex DAX, so that would work for you. As long as it is based on the same source of data, your numbers should be the same.