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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dillard70
Regular Visitor

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?

1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-jiascu-msft
Employee
Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

Thank you Dale, that works perfectly!

Dillard70
Regular Visitor

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:

 

Capture.PNG 

 

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. 

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. 

Capture1.JPG

 

 

ArulselvanD07
Helper I
Helper I

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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