cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular 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:

sample.PNG 

We would like to have a graphical representation like this :

1.PNG

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 Smiley Happy

StatusName.PNG

We try many other things without success Smiley Happy

Thank you for your help.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Need Help in DAX ! :)

Hi @pira,

 

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

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

View solution in original post

6 REPLIES 6
Highlighted
Super User II
Super User II

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 to be a Super User!




Highlighted
Regular Visitor

Re: Need Help in DAX ! :)

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

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

 

Highlighted
Super User II
Super User II

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 to be a Super User!




Highlighted
Microsoft
Microsoft

Re: Need Help in DAX ! :)

Hi @pira,

 

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

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

View solution in original post

Highlighted
Regular Visitor

Re: Need Help in DAX ! :)

Hi @vanessafvg,

 

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

Highlighted
Regular Visitor

Re: Need Help in DAX ! :)

Hi @v-ljerr-msft,

 

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors