topic Re: Filters with Distinct Count in DAX Commands and Tips

Filters with Distinct Count

dolevh — Wed, 10 Nov 2021 11:59:40 GMT

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?

Re: Filters with Distinct Count

Greg_Deckler — Wed, 10 Nov 2021 11:52:14 GMT

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

Re: Filters with Distinct Count

dolevh — Wed, 10 Nov 2021 12:03:09 GMT

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

Greg_Deckler

Re: Filters with Distinct Count

VahidDM — Thu, 11 Nov 2021 00:03:36 GMT

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/

Re: Filters with Distinct Count

v-yanjiang-msft — Mon, 15 Nov 2021 01:39:24 GMT

Hi dolevh ,

According to your description, here’s my solution.

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.