Filters with Distinct Count

[dolevh]

Total Revenue per Month 3 Table

ydCode	Blocker Boolean num	ydDeptName	StartofMonth
123	1	Electrics	1/1/21
123	1	Electrics	1/2/21
123	1	Electrics	1/3/21
123	1	Electrics	1/4/21
123	1	Electrics	1/5/21
321	0	Hybrid	1/1/21
321	0	Hybrid	1/2/21
321	0	Hybrid	1/3/21
321	0	Hybrid	1/4/21
321	0	Hybrid	1/5/21
555	1	Gasoline	1/1/21
555	1	Gasoline	1/2/21
555	1	Gasoline	1/3/21
444	1	Hybrid	1/1/21
444	1	Hybrid	1/2/21
444	1	Hybrid	1/3/21

Measure =
CALCULATE (
DISTINCTCOUNT ('Total Revenue per Month 3 Table'[ydCode]),
FILTER(
ALL(
'Total Revenue per Month 3 Table','Total Revenue per Month 3 Table'[Blocker boolean num]),
'Total Revenue per Month 3 Table'[Blocker boolean num] = "1"),
FILTER(
ALL('Total Revenue per Month 3 Table'[ydDeptName]),
'Total Revenue per Month 3 Table'[ydDeptName] <> "Gasoline")) 

Does someone know why it's not working?

[v-yanjiang-msft]

Hi @dolevh ,

According to your description, here’s my solution.

1. Create a measure.

Measure = IF(MAX([Blocker boolean num])=1&&MAX([ydDeptName])<>"Gasoline"&&MONTH(MAX('Table'[StartofMonth]))=1,"1","0")

2.Put the measure into filter, and then select 1.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VahidDM · Accepted solution]

Hi @dolevh 

If you are looking for a measure to count the number of ydCode values based on the filters ([Blocker boolean num] = 1 and [ydDeptName] <> "Gasoline"), and 2 as a result, try this measure:

Measure = 
CALCULATE (
    DISTINCTCOUNT ( 'Total Revenue per Month 3 Table'[ydCode] ),
    FILTER (
        ALL ( 'Total Revenue per Month 3 Table' ),
        'Total Revenue per Month 3 Table'[Blocker boolean num] = 1
            && 'Total Revenue per Month 3 Table'[ydDeptName] <> "Gasoline"
    )
)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Greg_Deckler]

@dolevh What is the end result you are trying to achieve? As in, what would be your expected result from the sample data?

[dolevh]

ydCode	Blocker Boolean num	ydDeptName	StartofMonth
123	1	Electrics	1/1/21
444	1	Hybrid	1/1/21

@Greg_Deckler