Beginner DAX syntax to combine unique count & another measure

[cklopper]

I'm a PowerBI user who is new to DAX. I connect to our data cube using SQL Analysis Services, so as I understand it there's limited ability to manipulate columns. I've been setting up a series of DAX measures, some of which are based on existing measures built into the cube.

I'm struggling with this one:

I want to count all opportunities in pipeline (ie, not equal to Deal Lost or Deal Won) that have more than 1 Division, and then allocate points to the outcome:

1. If unique opportunity (based on "OpportunityCode") contains 2 unique Divisions, then allocate 2 points
2. If unique opportunity contains 3 unique Divisions, then allocate 4 points
3. If unique opportunity contains 4+ unique Divisions, then allocate 6 points

There is an existing measure in the cube called "Total Count of Pipeline Opportunities", which counts the opportunities in Pipeline "OpportunityStage" (exclude opportunities where "OpportunityStage" is Deal Won or Deal Lost).

For other measures I've been using a combination of CALCULATE and SWITCH, but trying to get a unique count of the Divisions has me stumped. It doesn't seem possible to combine CALCULATE and DISTINCTCOUNT.

Any suggestions on what syntax to use? Help would be really appreciated!

Data sample:

AnonAccountName	OpportunityCode	Division	SFDC ACV GP	OpportunityStage
NAME001	INVLD00015BjhCQAS	SD1	4444.44	Identification
NAME002	INVLD00014rst0QAA	SD2	2000	Proposal
NAME003	INVLD00014rEoTQAU	SD3	0	Deal Lost
NAME004	INVLD00014rlK7QAI	SD4	3000	Deal Lost
NAME005	INVLD00015BWKQQA4	SD4	0	Finalist
NAME006	INVLD00013tVXqQAM	SD1	20583.33	Deal Won
NAME007	INVLD00015EH65QAG	SD4	15492.22	Proposal
NAME007	INVLD00015EH65QAG	SD5	1440	Proposal
NAME007	INVLD00015EH66QAG	SD3	207.78	Qualification
NAME007	INVLD00015EH66QAG	SD4	15998.89	Qualification
NAME007	INVLD00015EHVpQAO	SD4	1147.78	Deal Lost
NAME008	INVLD00015EH1kQAG	SD3	3902.6	Deal Lost
NAME008	INVLD00015EHHkQAO	SD4	116.88	Deal Lost
NAME009	INVLD00014rGMHQA2	SD7	3760	Deal Lost
NAME009	INVLD00015ByPnQAK	SD4	58	Deal Lost
NAME010	INVLD00014s54eQAA	SD2	2496	Deal Lost
NAME010	INVLD00014s54jQAA	SD2	2496	Deal Lost
NAME010	INVLD00014s54oQAA	SD2	2496	Deal Lost
NAME010	INVLD00014s54ZQAQ	SD2	2496	Deal Lost
NAME010	INVLD00015BlbrQAC	SD5	1544	Deal Won
NAME010	INVLD00015BlbSQAS	SD5	1248	Deal Won
NAME010	INVLD00015CcNIQA0	SD5	1544	Deal Won
NAME010	INVLD00015CcNNQA0	SD5	1248	Deal Won
NAME011	INVLD00014rFSnQAM	SD5	37838	Deal Lost
NAME012	INVLD00015Buf4QAC	SD6	54545.45	Deal Lost
NAME013	INVLD00012BxHpQAK	SD1	41666.67	Identification
NAME013	INVLD00015CyeBQAS	SD7	50	Deal Won
NAME014	INVLD00015BgFTQA0	SD4	4320	Proposal Evaluation
NAME014	INVLD00015BgFTQA0	SD5	900	Proposal Evaluation
NAME015	INVLD00014rFcdQAE	SD7	20000	Deal Lost
NAME015	INVLD00014rFceQAE	SD7	65144.73	Deal Won
NAME015	INVLD00014rFcYQAU	SD7	123.33	Deal Lost
NAME016	INVLD00013riOkQAI	SD1	222222.22	Qualification
NAME016	INVLD00014qNbmQAE	SD1	666666.67	Requirements Definition
NAME017	INVLD00015EHUOQA4	SD3	68863.33	Qualification
NAME018	INVLD00015BmnUQAS	SD4	277	Deal Won
NAME018	INVLD00015BmocQAC	SD5	8421	Deal Won
NAME018	INVLD00015BmoNQAS	SD4	28	Deal Won
NAME018	INVLD00015CbbsQAC	SD4	277	Deal Won
NAME018	INVLD00015CbbYQAS	SD5	8421	Deal Won
NAME018	INVLD00015CbcgQAC	SD4	28	Deal Won
NAME019	INVLD00014rFUEQA2	SD2	20000	Proposal
NAME020	INVLD00014rqO4QAI	SD6	25131	Deal Won
NAME020	INVLD00015DaSiQAK	SD6	17000	Finalist
NAME021	INVLD000138jUvQAI	SD1	277777.78	Proposal
NAME022	INVLD00014rGQ7QAM	SD7	701.6	Deal Lost
NAME023	INVLD00015CmJRQA0	SD1	25500	Proposal
NAME024	INVLD00015CEVXQA4	SD6	1111.11	Proposal
NAME025	INVLD00015CjBdQAK	SD4	657	Deal Won
NAME026	INVLD00014rEfgQAE	SD7	0	Proposal
NAME027	INVLD00015C36DQAS	SD3	39238	Deal Lost
NAME028	INVLD00014rUnbQAE	SD2	33333	Finalist
NAME029	INVLD00015BvOwQAK	SD2	444.44	Deal Lost
NAME030	INVLD00015EovxQAC	SD4	1650	Proposal
NAME031	INVLD00014r2jyQAA	SD3	188136	Deal Won
NAME031	INVLD00014r2lVQAQ	SD3	3600	Qualification
NAME031	INVLD00014r2lVQAQ	SD1	7685	Qualification
NAME031	INVLD00014r2nRQAQ	SD3	7200	Deal Won
NAME031	INVLD00014rJe7QAE	SD3	6667	Requirements Definition
NAME031	INVLD00014rTxeQAE	SD3	2000	Qualification
NAME031	INVLD00015ERYNQA4	SD3	44040	Deal Won
NAME032	INVLD00015EGraQAG	SD1	8390.91	Deal Won
NAME032	INVLD00015EH6kQAG	SD1	15305.19	Deal Won
NAME033	INVLD00013s7lMQAQ	SD5	46379.31	Finalist
NAME033	INVLD00013s7lMQAQ	SD6	46379.31	Finalist
NAME033	INVLD00015DCLDQA4	SD7	60940.69	Proposal
NAME034	INVLD00015D0KVQA0	SD7	833.33	Deal Lost
NAME034	INVLD00015EGoNQAW	SD4	376.67	Proposal Evaluation
NAME034	INVLD00015EGXDQA4	SD7	4108.89	Proposal
NAME034	INVLD00015EHCHQA4	SD5	1600	Identification
NAME034	INVLD00015EHRCQA4	SD2	694.44	Requirements Definition
NAME034	INVLD00015EHRtQAO	SD4	412.22	Deal Lost
NAME035	INVLD00015EHbYQAW	SD4	1088.89	Deal Won
NAME035	INVLD00015EHc1QAG	SD4	111194.44	Identification
NAME035	INVLD00015EHgTQAW	SD4	31468.89	Requirements Definition
NAME036	INVLD00014qirwQAA	SD1	2613.33	Deal Won
NAME036	INVLD00014qisBQAQ	SD1	2613.33	Deal Won
NAME036	INVLD00014qisQQAQ	SD1	2613.33	Deal Won
NAME036	INVLD00014sUMVQA2	SD1	66666.67	Finalist
NAME036	INVLD00014sUNEQA2	SD3	100000	Identification
NAME036	INVLD00014VJcyQAG	SD1	2613.33	Deal Won
NAME036	INVLD00014VJd3QAG	SD1	2613.33	Deal Won
NAME036	INVLD00014VJd4QAG	SD1	2613.33	Finalist
NAME036	INVLD00015BupIQAS	SD1	24000	Finalist
NAME036	INVLD00015BuqBQAS	SD1	120000	Finalist
NAME036	006D000000wv1CMIAY	SD3	1600	Proposal Evaluation
NAME037	INVLD00015EHbeQAG	SD4	91.11	Deal Won
NAME038	INVLD00015CQVjQAO	SD7	7578.28	Deal Won
NAME039	INVLD00013t8ZlQAI	SD1	7500	Deal Lost
NAME040	INVLD00015CdzzQAC	SD4	663	Deal Won
NAME040	INVLD00015CdzzQAC	SD5	130	Deal Won
NAME041	INVLD00013qcbEQAQ	SD1	23333.33	Proposal Evaluation
NAME042	INVLD00015CvWdQAK	SD5	1098	Finalist
NAME043	006D000000wuxk5IAA	SD1	4630	Deal Lost
NAME044	INVLD00014rGbLQAU	SD7	2203.45	Identification
NAME044	INVLD00014rGbLQAU	SD5	1293.1	Identification
NAME044	INVLD00014rGbLQAU	SD7	22629.31	Identification
NAME044	INVLD00014rGbNQAU	SD2	15595.54	Proposal
NAME044	INVLD00014rPxCQAU	SD7	2203.45	Deal Won
NAME044	INVLD00015D44LQAS	SD7	52.63	Deal Won
NAME044	INVLD00015Esd5QAC	SD3	5100	Proposal Evaluation
NAME045	INVLD00014rMrmQAE	SD4	129.49	Deal Won
NAME045	INVLD00014sHUzQAM	SD4	938	Deal Lost
NAME045	INVLD00014sHUzQAM	SD5	18	Deal Lost
NAME045	INVLD00015BKRcQAO	SD4	556	Finalist

[dedelman_clng]

Hi @cklopper  - 

Try one of these

DivisionCount = CALCULATE( COUNTROWS( DISTINCT( Table[Division] ) )

or

DivisionCount = CALCULATE( COUNTROWS( VALUES( Table[Division] ) )

or (this is useful if there is a filter somewhere on Division)

DivisionCount = CALCULATE( COUNTROWS( ALL ( Table[Division] ) )

Hope this helps

David

[cklopper]

Thanks so much - will try it out later today and let you know what happens. 🙂