Extracting a cell value in a dataset in Power BI and present into Bar Chart

[ANNING]

Hi all,

I am trying to extract out a cell value in my loaded excel dataset and present it as a bar chart visualisation.

The following is a transformed dataset in Power BI.

Question:

if you look at the above table, I would those numerical figure shown in blue in column "Done" to be presented into the visual in the following format, and may I know how to achieve this through DAX or M query during transformation?

Hope you can shed some light here.

Regards,
Anning

[ibarrau · Accepted solution]

Hi, it looks like you want to show the last date tasks in done for each sprint. You can accomplish that kind of logic with DAX. However, if we consider that each day the "done" column will increase its tasks then we can just consider the max value. Try just adding a bar char with axis = sprint column and value = (max) done column. The done column must be numeric and it will have the maximum option in the small down arrow of the value property of the bar chart.

If the previous logic won't work for you because you know you can have less tasks in done the following day of a sprint, let us know so we can build the DAX for the original logic.

Regards, hope this works

[v-alq-msft]

Hi, @ANNING 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a measure as below.

Result = 
var maxdate = 
CALCULATE(
    MAX('Table'[Day_Date]),
    FILTER(
        'Table',
        NOT(ISBLANK([New]))&&
        NOT(ISBLANK([Active]))&&
        NOT(ISBLANK([Done]))
    )
)
return
COALESCE(
    CALCULATE(
        MAX('Table'[Done]),
        FILTER(
            'Table',
            [Day_Date]=maxdate&&
            NOT(ISBLANK([New]))&&
            NOT(ISBLANK([Active]))&&
            NOT(ISBLANK([Done]))
        )
    ),0
)

Result:

Best Regards

Allan

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

[ibarrau · Accepted solution]

Hi, it looks like you want to show the last date tasks in done for each sprint. You can accomplish that kind of logic with DAX. However, if we consider that each day the "done" column will increase its tasks then we can just consider the max value. Try just adding a bar char with axis = sprint column and value = (max) done column. The done column must be numeric and it will have the maximum option in the small down arrow of the value property of the bar chart.

If the previous logic won't work for you because you know you can have less tasks in done the following day of a sprint, let us know so we can build the DAX for the original logic.

Regards, hope this works

[ANNING]

@ibarrau 

Thanks for your feedback.

I used Max function previously and unfortunately, it is almost correct but it is not 100% correct because the correct value for "Done" status not neccessary a maximum value as show in the following business condition where it is 47 not 52. 

Hence, if I use MAX function, the result for all Done result will be correct for Sprints except the Done result in Sprint 1

If there is a DAX that can identify the last Done value (i.e. last row of each relevant sprint), it will works. 

I tried to use LastNonBlank function but it is still not working as my understanding is that LastNonBlank operate in a sorted column by nature. Can we force LastNonBlank function not to operate in a sorted nature? or other function can do this trick?

LastNonBlank = LASTNONBLANK('BD_Chart'[Done], 1)

Hope there is a way to fix this. 

Thanks.

[ibarrau · Accepted solution]

Oh! I haven't seen that one. Don't worry. Let's use DAX ! It should look like this:
lastvaluefromdate

NewMeasure = 
VAR _last_date = MAX('Table'[Day_Date])
RETURN
CALCULATE(
    SUM('Table'[Done])
    , 'Table'[Day_Date] = _last_date
)

First capture the last date with MAX for a Sprint. This will work because it will solve it for an aggregation that in the visualization is a sprint. Then you calculate the Done for that specific date even if it says "SUM".

Hope that helps!