Problem using DISTINCTCOUNT with multiple filters

[ineedham]

I would like to calculate the distinct count of values in a column on a filtered list to use in a formula.

I’ve read the other posts on this, but am still stumped.

I have a larger dataset that I have narrowed down to 14 rows/purchases by using  filters from 3 distinct fields (Amount, Item Description, and Purchase Date).

Name	ID	Invoice Number	Transaction Date	Item Description	Amount
Abraham Simpson	0003	0032	8/22/2016	Ben Gay	$3.07
Barney Gumble	0015	0042	12/25/2016	Duff Beer	$192.00
Bart Simpson	0006	0013	8/8/2017	Cherry Bombs	$2.50
Homer Simpson	0004	0006	6/30/2017	Duff Beer	$9.99
Homer Simpson	0004	0004	12/28/2017	Donuts	$5.29
Krusty The Clown	0020	0047	9/19/2017	Makeup	$18.00
Lisa Simpson	0007	0017	8/22/2016	Saxophone Reed	$18.00
Maggie Simpson	0008	0031	5/28/2016	Pacifiers	$9.99
Marge Simpson	0005	0009	9/14/2017	Tomatoes	$3.17
Milhouse Van Houten	0026	0054	3/5/2017	Comic Books	$4.99
Milhouse Van Houten	0026	0053	3/5/2016	Squishee	$1.19
Moe Szyslak	0014	0040	8/27/2016	Duff Beer	$477.00
Ned Flanders	0022	0049	6/7/2016	Hymn Book	$14.00
Seymour Skinner	0030	0058	6/28/2016	Neck Tie	$16.99

The table shows the 14 remaining transactions, but there are only 12 distinct individuals who made those purchases (Homer and Milhouse each have 2 purchases; everyone else, just 1).

I want to multiply the distinct number of people on the filtered list by a constant: 100. I am looking to return 100 for every one of the 12 distinct people listed in the filtered table. In this case, the total I want is 1,200.

I’ve tried a couple different ways.  The first is simple and assumes the filters are applied to the PBIX file itself:

Est Value Simple =
100 * CALCULATE ( DISTINCTCOUNT ( 'Springfield Purchases'[ID] ) )

I also tried with the filters in the formula itself:

Est Value w Filters =
100
    * CALCULATE (
        CALCULATE (
            DISTINCTCOUNT ( 'Springfield Purchases'[ID] ),
            FILTER ( 'Springfield Purchases', 'Springfield Purchases'[Amount] < 500 ),
            FILTER (
                'Springfield Purchases',
                'Springfield Purchases'[Item Description] <> "Unknown"
            ),
            FILTER (
                'Springfield Purchases',
                'Springfield Purchases'[Purchase Date] >= DATE ( 2016, 1, 1 )
            )
        )
    )

Both of them return the same value: 1400 instead of the 1200 I’m looking for.

[v-caliao-msft · Accepted solution]

@ineedham,

I tested it on my local environment by using your sample data and formula, everything works fine.

Regards,

Charlie Liao

[v-caliao-msft · Accepted solution]

@ineedham,

I tested it on my local environment by using your sample data and formula, everything works fine.

Regards,

Charlie Liao

[ineedham]

This is strange. 

Here's a link to the PBIX file:  

https://drive.google.com/open?id=1uPhi9HIF-cz8ikIJL0AfMIbKuk7WaNzM

Also, I tried ditching the larger dataset, and only importing the 14 records in the preview.  I still get 1400 using the formulas.  It's as if DISTINCTCOUNT is being treated as COUNT in my formulas.

[ineedham · Accepted solution]

Got it.  It works properly as a Measure, but not as a Calculated Column.  See this post for reference:  

https://community.powerbi.com/t5/Desktop/Distinct-Count-not-working/td-p/183519