distinct count with expressions evaluate in selected period

[giorgiokatr]

hi there

date	Customer_id	product a Purchased times	product b Purchased times	Year	Month
31/1/2015	1140009	0	1	2015	1
28/2/2015	1140009	0	1	2015	2
31/3/2015	1140009	0	1	2015	3
30/4/2015	1140009	0	2	2015	4
31/5/2015	1140009	1	2	2015	5
30/6/2015	1140009	0	2	2015	6
31/7/2015	1140009	0	2	2015	7
31/8/2015	1140009	0	1	2015	8
30/9/2015	1140009	0	1	2015	9
31/10/2015	1140009	1	1	2015	10
30/11/2015	1140009	0	1	2015	11
31/12/2015	1140009	0	1	2015	12

i would like to distinct count the Customers when product b Purchased but product a did non Purchased

i used

product b Purchased only= CALCULATE(DISTINCTCOUNT(table[Customer_id]);table[product b Purchased times]>0;table[product a Purchased times]=0) 

it works great when i select one month 

for example if i select 2015-1 i have a 1 

if i select 2015-10 i have a null

is it possible when i select for example in my filters all 2015  and months to have distinct count null 

because i would like to show the customers byued product b but not  a in the selected year and this should be in this specific table none.

[v-yulgu-msft · Accepted solution]

Hi @giorgiokatr,

Please refer to below measures:

Measure peuchase a =
CALCULATE (
    SUM ( 'table'[product a Purchased times] ),
    ALLSELECTED ( 'table'[date] )
)

Measure purchase b =
CALCULATE (
    SUM ( 'table'[product b Purchased times] ),
    ALLSELECTED ( 'table'[date] )
)

product b Purchased only =
CALCULATE (
    DISTINCTCOUNT ( 'table'[Customer_id] ),
    FILTER (
        SUMMARIZE (
            'table',
            'table'[Customer_id],
            "a", SUM ( 'table'[product a Purchased times] )
        ),
        [Measure purchase b] > 0
            && [Measure peuchase a] = 0
    )
)

Also, I have uploaded my .pbix file for your reference.

Best regards,
Yuliana Gu

[giorgiokatr]

Thanks it works perfect! Just a question

you filter a summarize table with the two criteria 

why it works when you use in filter the measures Measure peuchase a  and  Measure peuchase b and dont just use SUM ( 'table'[product a Purchased times] ?

what is the purpose of allselected?

[v-yulgu-msft]

Hi @giorgiokatr,

The most difference between measures [Measure peuchase a], [Measure peuchase b] and SUM ( 'table'[product a Purchased times]) is the context. You could add [Measure peuchase a] and  [Measure peuchase b] into table visual, compare their values with SUM ( 'table'[product a Purchased times]) in each row.

Here is a blog for your reference to better understand ALLSELECTED.

Regards,
Yuliana Gu

[giorgiokatr]

Thanks!