Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jeyeline
Helper II
Helper II

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

FieldValue
TeamInternal, External
Reference NumberNull, 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

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!

 

View solution in original post

9 REPLIES 9
v-cgao-msft
Community Support
Community Support

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

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!

 

Thanks, will try this and update

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

@lbendlin , the sample data

IDWork Item TypeCreated DateTeamReference NumberClosed Date
123Bug22-03-2023Internal 31-03-2023
124Bug22-03-2023External 1-04-2023
125Bug25-03-2023ExternalJ121-04-2023
126Bug25-03-2023ExternalJ131-04-2023
127Bug25-03-2023ExternalNA1-04-2023

Something like this?

 

lbendlin_0-1710004847732.png

@dm-p 

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

Teach your users how to operate the filter pane.

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors