Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ANNING
Helper I
Helper I

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

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.

Extracting a value in a table cell in Power BI.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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?

barchart.JPG

 

 

 

 

 

 

 

Hope you can shed some light here.

 

Regards,
Anning

2 ACCEPTED SOLUTIONS
ibarrau
Super User
Super User

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


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

Happy to help!

LaDataWeb Blog

View solution in original post

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

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!


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

Happy to help!

LaDataWeb Blog

View solution in original post

4 REPLIES 4
v-alq-msft
Community Support
Community Support

Hi, @ANNING 

 

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

Table:

a1.png

 

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:

b2.png

 

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
Super User
Super User

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


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

Happy to help!

LaDataWeb Blog

@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. 

ANNING_0-1602807334848.png

 

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

ANNING_3-1602807653547.png

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)
 

ANNING_0-1602809202238.png

 

Hope there is a way to fix this. 

Thanks.

 

 

 

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

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!


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

Happy to help!

LaDataWeb Blog

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors