Forum Discussion

pira's avatar
pira
Regular Visitor
9 years ago
Solved

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 :smileyhappy:

We try many other things without success :smileyhappy:

Thank you for your help.

Need Help
Show and Tell
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi pira,

     

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

    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

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi pira,

     

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

    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

    • pira's avatar
      pira
      Regular Visitor
      9 years ago

      Hi v-ljerr-msft,

       

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

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    9 years ago

    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

    • pira's avatar
      pira
      Regular Visitor
      9 years ago

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

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

       

      • vanessafvg's avatar
        vanessafvg
        Community Champion
        9 years ago

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