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" column. I cannot simply add a visualization level filter that removes all rows where "Credit Score" is blank, because in the same visualization I need to show the total of new leads each month which includes leads with no credit score. cannot seem to find a DAX function that allows you to both count the rows in a column that equal "Started" and then filters those results by blank rows under another column. 

 

Here is what I have so far:

 

Started = CALCULATE(COUNTAX('Merged Reports',[Data.Last Finished Milestone]),'Merged Reports'[Data.Last Finished Milestone]="Started")

 

Is there a way to filter this by the results of another column? Do I need to create a new measure that counts the results of this one, and then filters by a separate column?

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

6 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Would be nice if this could be setup in the Quick Measurement... Could save people some time

  • ArulselvanD07's avatar
    ArulselvanD07
    Helper I
    8 years ago

    Could you please share the Data and the result you are looking for? Also share the relationship details for your datasets [can be found on the relationships tab

  • 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.