Creating a measure with multiple conditions

[Rsanjuan]

I am looking to calculate # of bids, when Job Phase equals certain conditions.  How do we do it for more than one condition?

NumberofSales = Calculate(DISTINCTCOUNT(Master[Bid_Number__c]),Master[Job.Job_Phase__c]="closed")

For example, I want to include not only "closed" but "in process", "confirmed"

Thanks!

[samdthompson · Accepted solution]

check that the calculated colum is working as expected. Without seeing more of the model its hard to see whats wrong. 

//if this is a solution please mark as such

[Anonymous]

I want to count in datset if NCR status is "Open" and Proposed Action Status, please help

Open & Accepted = CALCULATE(COUNT(NCR[NCR Closeout Status]),NCR[NCR Closeout Status]="Open"&& NCR[Proposed Action Status]="Accepted"))

[GILLOJUNKUMAR]

Thanks Bro.. This was solved my requirement...😀

[Ashish_Mathur]

Hi,

I do not see any mistake in your formula.  What problem are you facing?

[Anonymous]

The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.

[Ashish_Mathur]

Hi,

If you replace && with a comma, does the formula work?  If not, then share the link from where i can download your PBI file.

[Anonymous]

Please help,

I want Calculated Row for No of Shop Drawings with Rev 0, 1 , 2 , 3 submitted by Subcontractor, The formula I used is working fine in while creating measure but its not working when I am creating calculated column

Calculated Column = CALCULATE (COUNT('Shop Drawings'[Subcontractor]),'Shop Drawings'[Subcontractor]="ACCESS 1", 'Shop Drawings'[Rev]="0")

Subcontractor	Revo 0	Rev 1	Rev 2	Rev 3
ACCESS 1	2	3	1	0

[Ashish_Mathur]

Hi,

Share the source dataset.  The table that you have pasted in your message looks like the result you are expecting.

[Anonymous]

Sl

Drawing Number

Rev

Plot Zone Code

Shop Drawing Title

Level

Zone

Discipline

Subcontractor

Transmittal Reference

Submission Date Planned

Submission Date Actual

Due Date

Actual

Overdue

Approval Status

Latest / Superseded

Approval Ref. No.

1

6004-0100-ACS-1112-SO-1000

0

0100

Typical Car Park Rubber Accessories Details

Architectural/CORNER GUARDS

ACCESS 1

6004-ALE-TRANSMIT-016047

7-Nov-15

14-Nov-15

21-Nov-15

5-Dec-15

14

B

superseded

6004-MLD-TRANSMIT-020768

2

6004-0100-ACS-1112-SO-1000

1

0100

Typical Car Park Rubber Accessories Details

Architectural/CORNER GUARDS

ACCESS 1

6004-ALE-TRANSMIT-040040

24-Mar-16

31-Mar-16

31-Mar-16

0

B

Latest

6004-MLD-TRANSMIT-042535

3

6004-0100-ACS-1112-SO-1001

0

0100

Car Park Accessory setting out. Basement 2. ZONE 3

Zone 3

Architectural/CORNER GUARDS

ACCESS 1

6004-ALE-TRANSMIT-018420

7-Nov-15

25-Nov-15

2-Dec-15

5-Dec-15

3

B

superseded

6004-MLD-TRANSMIT-020732

4

6004-0100-ACS-1112-SO-1001

1

0100

Car Park Accessory setting out. Basement 2. ZONE 3

Zone 3

Architectural/CORNER GUARDS

ACCESS 1

6004-ALE-TRANSMIT-062382

25-Sep-16

2-Oct-16

15-Oct-16

13

B

Latest

6004-MLD-TRANSMIT-065308

5

6004-0100-ACS-1112-SO-1002

0

0100

Car Park Accessory setting out. Basement 2. ZONE 2

Zone 2

Architectural/CORNER GUARDS

ACCESS 1

6004-ALE-TRANSMIT-016048

7-Nov-15

14-Nov-15

21-Nov-15

5-Dec-15

14

B

Latest

6004-MLD-TRANSMIT-020765

6

6004-0100-ACS-1112-SO-1003

0

0100

Car Park Accessory setting out. Basement 2. ZONE 3

Zone 3

Architectural/CORNER GUARDS

ACCESS 1

6004-ALE-TRANSMIT-016050

7-Nov-15

14-Nov-15

21-Nov-15

5-Dec-15

14

B

superseded

6004-MLD-TRANSMIT-020749

7

6004-0100-ACS-1112-SO-1003

1

0100

Car Park Accessory setting out. Basement 2. ZONE 3

Zone 3

Architectural/CORNER GUARDS

ACCESS 1

6004-ALE-TRANSMIT-062385

25-Sep-16

2-Oct-16

15-Oct-16

13

B

Latest

6004-MLD-TRANSMIT-065305

8

6004-0100-ACS-1112-SO-1004

0

0100

Car Park Accessory setting out. Basement 2. ZONE 4

Zone 4

Architectural/CORNER GUARDS

ACCESS 1

6004-ALE-TRANSMIT-016051

14-Nov-15

14-Nov-15

21-Nov-15

5-Dec-15

14

B

superseded

6004-MLD-TRANSMIT-020752

9

6004-0100-ACS-1112-SO-1004

1

0100

Car Park Accessory setting out. Basement 2. ZONE 4

Zone 4

Architectural/CORNER GUARDS

ACCESS 1

6004-ALE-TRANSMIT-062393

25-Sep-16

2-Oct-16

15-Oct-16

13

B

Latest

6004-MLD-TRANSMIT-065307

10

6004-0100-ACS-1112-SO-1005

0

0100

Car Park Accessory setting out. Basement 2. ZONE 5

Zone 5

Architectural/CORNER GUARDS

ACCESS 1

6004-ALE-TRANSMIT-016052

14-Nov-15

14-Nov-15

21-Nov-15

5-Dec-15

14

B

Latest

6004-MLD-TRANSMIT-020753

11

6004-0100-ACS-1112-SO-1006

0

0100

Car Park Accessory setting out. Basement 2. ZONE 6

Zone 6

Architectural/CORNER GUARDS

ACCESS 1

6004-ALE-TRANSMIT-016053

14-Nov-15

14-Nov-15

21-Nov-15

5-Dec-15

14

B

Latest

6004-MLD-TRANSMIT-020755

[Ashish_Mathur]

Hi,

In a Matrix visual, drag Contractor to row labels and Rev to column labels.  Write thie measure

=COUNTORWS(Data)

[Anonymous]

Thanks its working

[Anonymous]

Thanks Ashish,

Its working now, repplace && by Cooma and its working

[Ashish_Mathur]

Hi,

If my reply helped, please mark it as Answer.

[eka8742]

How can the result (15 in this case) be sliced versus shown as a static number?

[v-caliao-msft · Accepted solution]

Hi @Rsanjuan,

You can use the follow DAX expression to get your expected result.
Count = CALCULATE(DISTINCTCOUNT(Master[ID]),FILTER(ALL(Master),Master[Job.Job_Phase__c]="closed"||Master[Job.Job_Phase__c]="in process"||Master[Job.Job_Phase__c]="confirmed"))

Regards,

Charlie Liao

[samdthompson]

Hi, the cool thing about calculate is you can put multiple filters after it so:

NumberofSales = Calculate(DISTINCTCOUNT(Master[Bid_Number__c]),

filter(Master[Job.Job_Phase__c])="closed",

filter(table[1])="condition1",

filter(table[2])="condition2",

filter(table[n])="condition_n")

should do the job for you.

//if this is a solution please mark as such.

[Rsanjuan]

@samdthompson

It seemed to not work. I entered this:

NumberofSales = Calculate(DISTINCTCOUNT(Master[Bid_Number__c]),Master[Job.Job_Phase__c]="closed",filter(Master, Master[Job.Job_Phase__c]="confirmed"))

Those conditions are all under the same table and same field "Job Phase".  Am I doing something wrong?

[samdthompson]

try this:

NumberofSales = Calculate(DISTINCTCOUNT(Master[Bid_Number__c]),

filter(Master[Job.Job_Phase__c])="closed",

filter(Master, Master[Job.Job_Phase__c])="confirmed")

// if this is a solution please mark as such

[Rsanjuan]

@samdthompson

It seemed to not work either.  Is there a way, I can just do a "Not equal"?  There is only one field that we cannot consider for job phase.

[samdthompson]

I am bit confused as to why it would not work. i guess you could do a calculated column

= if(and(Master[Job.Job_Phase__c]="closed",Master, Master[Job.Job_Phase__c]="confirmed"),1,0)

and then do :

NumberofSales = Calculate(DISTINCTCOUNT(Master[Bid_Number__c]),filter(Master[calculated column])="1"))

//if this is a solution please mark as such

[Rsanjuan]

@samdthompson

I did this:

ConditionSales = if(and(Master[Job.Job_Phase__c]="closed",Master[Job.Job_Phase__c]="confirmed"),1,0)

then did this:

NoOfSales = Calculate(DISTINCTCOUNT(Master[Bid_Number__c]),Master[ConditionSales]="1")

When I try to put it into a card visual, I get this error:

Error Message:

MdxScript(Model) (1, 83) Calculation error in measure 'Master'[NoOfSales]: DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.