cancel
Showing results for
Search instead for
Did you mean:
Highlighted
Frequent Visitor

## Need Help in DAX ! :)

Hello everybody,

I'm a beginner on Power Bi and actually I have some difficulties with DAX language.

Since I still can't find a solution to my problem, I've decided to post my question here.

I have a database with a list of bugs of an application. Each bug has a status (new, assigned, resolved, closed…) and we have the date of the status modification.

Here are a sample of my data:

We would like to have a graphical representation like this :

For each day, we would like to know the number of bugs for each status.

In our database, we do not have data one row per day per bug, but only one row when the status modification has been done.

We try to display the cumulative result of bug ID per date and per StatusName but it’s not what we want

We try many other things without success

Thank you for your help.

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Need Help in DAX ! :)

Hi @pira,

Based on my test, the formula below should work in your scenario.

Measure =
VAR currentStatus =
FIRSTNONBLANK ( Table1[NewStatusName], 1 )
VAR currentDate =
MAX ( Table1[ModifyDate] )
RETURN
COUNTROWS (
FILTER (
SUMMARIZE (
FILTER ( ALL ( Table1 ), Table1[ModifyDate] <= currentDate ),
Table1[bug_id],
"MaxDate", MAX ( Table1[ModifyDate] ),
"LastStatus", CALCULATE (
FIRSTNONBLANK ( Table1[NewStatusName], 1 ),
FILTER (
Table1,
Table1[ModifyDate] = MAX ( Table1[ModifyDate] )
&& Table1[Index] = MAX ( Table1[Index] )
)
)
),
[LastStatus] = currentStatus
)
)

Note: You need to add an Index Column to your table under Query Editor > Add Column tab > Index Column first.

Regards

6 REPLIES 6
Super Contributor

## Re: Need Help in DAX ! :)

you need to create a date table that has all the dates and then link that to your transaction table on the date and that should sort out your issue

Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Frequent Visitor

## Re: Need Help in DAX ! :)

I've already tried it but  it does not solve the problem.

When the status of a bug is changed from New to Assigned for example, the number of bugs in the status New doesn't decreased (as it is a cumulative result).

Super Contributor

## Re: Need Help in DAX ! :)

@pira you will probably have to create a separate calculated measure for each status and plot them separately i am thinking?

Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Super Contributor

## Re: Need Help in DAX ! :)

Hi @pira,

Based on my test, the formula below should work in your scenario.

Measure =
VAR currentStatus =
FIRSTNONBLANK ( Table1[NewStatusName], 1 )
VAR currentDate =
MAX ( Table1[ModifyDate] )
RETURN
COUNTROWS (
FILTER (
SUMMARIZE (
FILTER ( ALL ( Table1 ), Table1[ModifyDate] <= currentDate ),
Table1[bug_id],
"MaxDate", MAX ( Table1[ModifyDate] ),
"LastStatus", CALCULATE (
FIRSTNONBLANK ( Table1[NewStatusName], 1 ),
FILTER (
Table1,
Table1[ModifyDate] = MAX ( Table1[ModifyDate] )
&& Table1[Index] = MAX ( Table1[Index] )
)
)
),
[LastStatus] = currentStatus
)
)

Note: You need to add an Index Column to your table under Query Editor > Add Column tab > Index Column first.

Regards

Frequent Visitor

## Re: Need Help in DAX ! :)

Hi @vanessafvg,

Sorry for my late response. I don't think that is the best solution ...

Frequent Visitor

## Re: Need Help in DAX ! :)

Thank you so much for your help. It works perfectly well !

## Helpful resources

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 129 members 2,072 guests
Recent signins:
Please welcome our newest community members: