Distinct Record count based on conditions in a context filter.

[78f111898f2543f]

Hey amigos! 

New to Power BI and I am used to Tableau's Level of Detail ability.  Here's my dataset which I have simplified for business reasons. I have a bunch of transactional data about widgets. I care about what district the transaction happened, the amount, the widget id, a widget rating, the widget model, and when the transaction happened. 

In one of my caluclations I have to multiple the widget rating by the number of months the user cares to see in the filters of the report. 

I have tried, but failed to produce the right number with TOTALMTD(DistinctCount('XTRN'[WidgetId]), 'XTRN'[TimePeriod]) which does not work, as it is not payting attention to the filters on the report page.

I started out simply with a if ('XTRN'[TransactionType] = "ABC", 1, 0) and summing that, but it does not give me a distinct count because sometimes a Widget shows up twice in a month. 

I have tried an calculate with an ALL, ALLSELECTED, ALLEXCEPT but those are ignoring the filtered critieria. 

What am I missing here? How do I get a unique count of monthly transactions for a filtered dataset? 

[v-juanli-msft · Accepted solution]

Hi @78f111898f2543f 

The reason why your formula is not changed with slicers may be that you create a calcualted column instead of a meaure.

you can create such measures which can change with slicers.

Measure = DISTINCTCOUNT('Table'[id])

Best Regards
Maggie

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

[amitchandak]

Please try.

You should able to add an additional filter?

TOTALMTD(DistinctCount('XTRN'[WidgetId]), 'XTRN'[TimePeriod],filter('XTRN','XTRN'[TransactionType]= "ABC"))
 

[78f111898f2543f]

The user is filtered by Disctrict and ReportingMonth, do I have to respecify all filters on a report? That seems like not a good solution or the intended design of Power BI.