Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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
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
Would be nice if this could be setup in the Quick Measurement... Could save people some time
Thank you Dale, that works perfectly!
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.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |