Copying the "First" behavior of a value in a calculated column

[Ayoeri]

First of all, I'm very new to PowerBi but I have no one in my organization to help and my google effords did not get me any further. Sorry if my question is super basic.

I've got below table:

For the each distinct project, I'm trying to calculate the lowest status of all the items Sub items in that project. If I make a table in the report view and put "Status" as value and list the summarization as "First" instead of count it gives me exactly what I want:

However, I tried to copy this behavior in my main table by adding a calculated table so I would have the lowest value status for each line item in a project.

I tried this with FIRSTNONBLANK ([Table] 'Status', [Table] 'Project'). If I understood the documentation correctly, this should return the first Status of the project. But I can't get this to work as it keeps giving me all the lowest status of that line item (which is per definition the status of that line item), rather than lowest status of the project the line item is part of. Can anyone explain how I get this done? Am I approahing this problem the wrong way?

 

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula

Lowest project status = calculate(min(Data[Status]),filter(Data,Data[Project]=earlier(Data[Project])))

Hope this helps.

[Anonymous · Accepted solution]

Hi @Ayoeri ,

First of all, many thanks to  for your very quick and effective replies.

Based on my testing, please try the following methods:

1.Create the simple table.

2.Create the new column to filter the lowest status.

Lowest Project status = CALCULATE(MIN('Table'[Status]), ALLEXCEPT('Table', 'Table'[Project], 'Table'[Sub Item]))

3.Besides, create the new measure to filter the lowest status.

Measure = MINX(FILTER(ALL('Table'), 'Table'[Project] = SELECTEDVALUE('Table'[Project])), 'Table'[Status])

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ayoeri]

Thanks. This worked.

As Step 3 from your solution was not using anything from step 2. So I removed the actions from Step 2 and the solution from Step 3 still worked. So it is not clear to me what the pupose was of Step 2?

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula

Lowest project status = calculate(min(Data[Status]),filter(Data,Data[Project]=earlier(Data[Project])))

Hope this helps.

[Ayoeri]

Thanks. This worked as well.

[Ashish_Mathur]

You are welcome.

[danextian]

Hi @Ayoeri 

Try this:

CALCULATE ( MIN ( tbl[status] ), ALLEXCEPT ( tbl, tbl[project], tbl[subitem] ) )

[Ayoeri]

Thanks for the response. Unfortunatly I could not get this to work. This gave me a repeat of the values in the status column (see screenshot below where I labelled your solution as "Trial Other"):

[vicky_]

Check out this article regarding Row Context on SQLBI: https://www.sqlbi.com/articles/row-context-in-dax/
(And also explore some of the other pages in the DAX 101 series, as I find that they are a great intro to DAX)

To fix your issue - you need to change your calculation for calculated column to something like:

Lowest Project Status = CALCULATE(MIN('Table'[Status]), ALLEXCEPT('Table', 'Table'[Project]))

Note - unless you absolutely NEED a calculated column, best practive would be to use a measure instead (the DAX for the measure would be the same in this case).

[Ayoeri]

Unfortunatly I could not get this to work. It just repeated the Status value.  Thanks for sharing the link.

The reason I was on the path of calculated columns was becuase I wanted to add this result in a hierachy. And this is not possible for measures if I'm not mistaken.