Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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 |
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
Thanks so much - will try it out later today and let you know what happens. 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |