cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Creating visualisation by summing the counts of two outputs

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?

1 ACCEPTED SOLUTION
Helper I

Hi @v-cgao-msft ,

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!

9 REPLIES 9
Community Support

Hi @Jeyeline ,

``````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

Helper I

Hi @v-cgao-msft ,

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!

Helper I

Thanks, will try this and update

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

Helper I

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

Something like this?

Helper I

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

Super User

Teach your users how to operate the filter pane.

Helper I

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors