Forum Discussion

ineedham's avatar
ineedham
Helper I
8 years ago
Solved

Problem using DISTINCTCOUNT with multiple filters

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).

 

 

NameIDInvoice NumberTransaction DateItem DescriptionAmount
Abraham Simpson000300328/22/2016Ben Gay$3.07
Barney Gumble0015004212/25/2016Duff Beer$192.00
Bart Simpson000600138/8/2017Cherry Bombs$2.50
Homer Simpson000400066/30/2017Duff Beer$9.99
Homer Simpson0004000412/28/2017Donuts$5.29
Krusty The Clown002000479/19/2017Makeup$18.00
Lisa Simpson000700178/22/2016Saxophone Reed$18.00
Maggie Simpson000800315/28/2016Pacifiers$9.99
Marge Simpson000500099/14/2017Tomatoes$3.17
Milhouse Van Houten002600543/5/2017Comic Books$4.99
Milhouse Van Houten002600533/5/2016Squishee$1.19
Moe Szyslak001400408/27/2016Duff Beer$477.00
Ned Flanders002200496/7/2016Hymn Book$14.00
Seymour Skinner003000586/28/2016Neck 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.

3 Replies