Creating visualisation by summing the counts of two outputs

[Jeyeline]

Hello Team,

 We are fetching data from Azure DevOps analytic view where we have certain fields like

Field	Value
Team	Internal, External
Reference Number	Null, NA, unique IDs

Null is when the field is left empty

Based on the data we have two charts 

1. X Axis span months - Y Axis sum of 'Team=external' on respective months
2.  X Axis span months - Y Axis sum of 'Reference Number is Null'

Now we would like to have a visualization with an x-axis spanning months and a y-axis should be the count of work item types that satisfy the condition:
first chart: Team=External, Reference Number != Null && NA
second chart: Team=External, Reference Number = NA

third chart: Team=Interal, Reference Number = NA

In what ways it could be accomplished, by measure, or how DAX can be incorporated here?

Can someone help with this?

[Anonymous]

Hi @Jeyeline ,

Please try these measures:

count of work item 1 = 
VAR __cur_year =
    SELECTEDVALUE ( 'Date'[Year] )
VAR __cur_month =
    SELECTEDVALUE ( 'Date'[Month Number] )
VAR __start_date =
    DATE ( __cur_year, __cur_month, 1 )
VAR __end_date =
    EOMONTH ( __start_date, 0 ) + 1
VAR __result =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[Created Date] >= __start_date
            && 'Table'[Created Date] < __end_date
            && 'Table'[Team] = "External"
            && 'Table'[Reference Number] IN { "", "NA" }
    ) + 0
RETURN
    __result

count of work item 2 = 
VAR __cur_year =
    SELECTEDVALUE ( 'Date'[Year] )
VAR __cur_month =
    SELECTEDVALUE ( 'Date'[Month Number] )
VAR __start_date =
    DATE ( __cur_year, __cur_month, 1 )
VAR __end_date =
    EOMONTH ( __start_date, 0 ) + 1
VAR __result =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[Created Date] >= __start_date
            && 'Table'[Created Date] < __end_date
            && 'Table'[Team] = "External"
            && NOT 'Table'[Reference Number] IN { "", "NA" }
    ) + 0
RETURN
    __result

count of work item 3 = 
VAR __cur_year =
    SELECTEDVALUE ( 'Date'[Year] )
VAR __cur_month =
    SELECTEDVALUE ( 'Date'[Month Number] )
VAR __start_date =
    DATE ( __cur_year, __cur_month, 1 )
VAR __end_date =
    EOMONTH ( __start_date, 0 ) + 1
VAR __result =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[Created Date] >= __start_date
            && 'Table'[Created Date] < __end_date
            && 'Table'[Team] = "Internal"
            && NOT 'Table'[Reference Number] IN { "", "NA" }
    ) + 0
RETURN
    __result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Jeyeline · Accepted solution]

Hi @Anonymous ,

 Thanks for the reply. I tried this and I customized it 

WorkItem1= CALCULATE (

     COUNTROWS( 'OKR'),  

              'OKR'[Team]="External"

         )

 WorkItem2= CALCULATE (

     COUNTROWS( 'OKR'),  

              'OKR'[Reference#] <> ""

         )

 And I combined the output by adding both to get the overall.

Thanks!

[Jeyeline]

Thanks, will try this and update

[lbendlin]

Now we would like to have a visualization with an x-axis spanning months

I don't see a date column in your sample data

[Jeyeline]

@lbendlin , the sample data

ID	Work Item Type	Created Date	Team	Reference Number	Closed Date
123	Bug	22-03-2023	Internal		31-03-2023
124	Bug	22-03-2023	External		1-04-2023
125	Bug	25-03-2023	External	J12	1-04-2023
126	Bug	25-03-2023	External	J13	1-04-2023
127	Bug	25-03-2023	External	NA	1-04-2023

[lbendlin]

Something like this?

@dm-p 

[Jeyeline]

Hi @lbendlin ,

 I want it to be line chart that shows the trend spanning months.

X axis will be based on the Month 

Goal is to determine, for example: for these months on axis Jan, Feb, Mar (created hierarchy)

- Count of Work item (Bug) which satisfies the condition (y-axis)

trend 1: Team must be External, Reference # equals either NA or empty

trend 2: Team must be External, Reference # equals neither NA nor empty

trend 3: Team must be Internal, Reference # equals either NA or empty

[lbendlin]

Teach your users how to operate the filter pane.

[Jeyeline]

Hello @lbendlin ,

We cannot accomplish this via the filter pane. Applying the first filter automatically filters the first level of records, hence there is no use in applying the second filter.

Our expectation is related to getting two separate outputs and combining both.