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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Jeyeline
Helper I
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

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors