Stumped with a calculated column

[Mouse]

I want to created a calculated column, not a measure.

I need the formula to filter within the table, to aproject, and then return the first nonblank sting from another column in the same table, using an index column to determin what that first position is.

In baby-speak I need filter to {ProjectID], retun first nonblank value from [Progress] where "first" is min [TaskID]

Results would look like the blue column. It is absolutely doing this newbie's head in. Please help!

[OwenAuger]

Thanks for testing that @Mouse  🙂

The blank values look good, and the DAX expression itself should be fine as it works in a test model at my end (attached for comparison).

Regarding the error, it looks like the type of the column First Progress might have been set incorrectly.
Can you check that it's set to text?

Alternatively, is there anything else in the query or DAX expression behind this table that might be causing type-related issues?

[Mouse]

omg I'm SUCH a muppet!!! That'll teach me to recycle a column. Owen you are a star. Thank you so very much.

[OwenAuger · Accepted solution]

Hi @Mouse 

Try this:

First Progress = 
CALCULATE (
    FIRSTNONBLANKVALUE (
        YourTable[Task Index],
        SELECTEDVALUE ( YourTable[Progress] ) -- auto context transition from FIRSTNONBLANKVALUE
    ),
    ALLEXCEPT ( YourTable, YourTable[ProjectId] )
)

The blank values in the Progress column must be genuinely blank (rather than empty strings) for this to work.

Does it work as expected for you?

[Mouse]

Hi Owen,

Thank you somuch for your reply, I really appreciate it!

Unfortunaely I got this error, which I've seen before in my attempts and I don't understand why.

Here are the outputs I'm trying to filter to, and the blank is (I think) a true blank as it's generated by the "else" of a switch calc that ends with Blank()