Forum Discussion

Dillard70's avatar
Dillard70
Regular Visitor
8 years ago
Solved

Creating Measure that counts rows in column with a specific value and filters by another column

I am trying to create a measure that counts the number of rows in the column "Last FInished Milestone" that equal "Started", but only counts rows that also have a value under the "Credit Score" colum...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi Dillard70,

     

    Try these similar formulas please.

    Started =
CALCULATE (
    COUNTAX ( 'Merged Reports', [Last Finished Milestone] ),
    'Merged Reports'[Last Finished Milestone] = "Started",
    ISBLANK ( 'Merged Reports'[Credit Score] ) = FALSE ()
)

    Started 2 = 
CALCULATE (
    COUNTAX ( 'Merged Reports', [Last Finished Milestone] ),FILTER(all('Merged Reports'),
    'Merged Reports'[Last Finished Milestone] = "Started"&&
    ISBLANK ( 'Merged Reports'[Credit Score] ) = FALSE ()
)
)

    Best Regards,

    Dale

Dillard70's avatar
Dillard70
Regular Visitor
8 years ago

I don't want to use specific data since it is personal information, but I can give an example here. I am using a merged report, so both columns are on the same table:

 

 

 

So, using this example, I would need to measure the count of "Last Finished Milestone" = "Started", but filter out the ones with blanks under the "Credit Score" column. 

  • ArulselvanD07's avatar
    ArulselvanD07
    Helper I
    8 years ago

    1. Create a measure for the Count of Last finished Milestone as COUNT(Sheet1[LastFinishedMilestone]).

    2. On the visual tab, select the measure you created and the LastFinishedMilestone columns.

    3. Drag the credit score column to the report level filter, select the filter type as Advanced Filtering and select is not blank .

     

    Your final visual will look like the below. I have selected Stacked Column chart for the visual.