Power BI (DAX): Distinct Count Filtered by Condition

excel excel-formula dax powerbi
AME picture AME · Feb 16, 2016 · Viewed 35.7k times · Source

I have a data set with publishers with revenue by month. Publishers are considered to be "active" month if their revenue is equal or greater than 1000 for a given month.

Now, I want to count the distinct number of "active" publishers over a monthly trend using Power BI (DAX).

Example Data Set

Example Data Set

Here is a measure formula that I attempted: 

DistCountActiveMonths = CALCULATE(DISTINCTCOUNT('Net Revenue Data'[Publisher Name]),FILTER('Net Revenue Data','Net Revenue Data'[Active Month]=1))

Please advise how to correct this formula to achieve the desired result, e.g.:

enter image description here

Answer

greggyb picture greggyb · Feb 16, 2016 
DistinctCountActiveMonths =
CALCULATE(
    DISTINCTCOUNT( 'Net Revenue Data'[Publisher Name] )
    ,'Net Revenue Data'[Active Month] = 1
)

Related questions

Calculating Percent of Total in Power Pivot Model

I have created a power pivot table as shown in the picture. I want to calculate quarter over quarter sales change. For which I have to divide for example corporate family "Acer" 's sales in 2012Q4 by sum of all …

Read More
Shortcut to Apply a Formula to an Entire Column in Excel

If I select a cell containing a formula, I know I can drag the little box in the right-hand corner downwards to apply the formula to more cells of the column. Unfortunately, I need to do this for 300,000 rows! Is …

Read More
Remove leading or trailing spaces in an entire column of data

How do I remove leading or trailing spaces of all cells in an entire column? The worksheet's conventional Find and Replace (aka Ctrl+H) dialog is not solving the problem.

Read More