Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

DAX - IF, AND & OR

Hello -

I'm trying to write a DAX formula but seem to be falling short, not sure if this can be achieved by 'adding a new column' within BI:

Example Question:

Within the UK, we have X amount of employees who are black or arabic and female - (i'm trying to find the number X).

Example Sample Data:

Current DAX formula:

= IF(OR((AND(Sheet1[Country]="UK",Sheet1[Gender]="Female"),(AND(Sheet1[Ethnicity]="Black",(Sheet1[Ethnicity]="Arabic")), "Yes", "No")))

Thank you in advance!

PijushRoy
4 years ago
Hi Anonymous

Try this one
Column = SWITCH(
TRUE(),
'Table'[Country] = "UK" && 'Table'[Gender] = "Female" && 'Table'[Ethnicity]="Black" || 'Table'[Ethnicity]="Arabic", "Yes",
"No")

Then you can countrows
= Calculate(Count('Table'[Country]),'Table'[Column] = "Yes")

If you have any question, please let me know.
If solve your requirement, please mark this answer as SOLUTION
If this comment helps you, please LIKE this comment/Kudos

You could do it with a calculated column but I would suggest a measure like:

# Needed = 
    CALCULATE (
        COUNTROWS ( Sheet1 ),
        Sheet1[Ethnicity] IN { "Black", "Arabic" },
        Sheet1[Gender] = "Female"
    )

4 Replies

bcdobbs
Community Champion
4 years ago
You could do it with a calculated column but I would suggest a measure like:
# Needed = CALCULATE ( COUNTROWS ( Sheet1 ), Sheet1[Ethnicity] IN { "Black", "Arabic" }, Sheet1[Gender] = "Female" )
- Anonymous
  Not applicable
  4 years ago
  Thanks Ben for the input, this also worked!
PijushRoy
Community Champion
4 years ago
Hi Anonymous

Try this one
Column = SWITCH(
TRUE(),
'Table'[Country] = "UK" && 'Table'[Gender] = "Female" && 'Table'[Ethnicity]="Black" || 'Table'[Ethnicity]="Arabic", "Yes",
"No")

Then you can countrows
= Calculate(Count('Table'[Country]),'Table'[Column] = "Yes")

If you have any question, please let me know.
If solve your requirement, please mark this answer as SOLUTION
If this comment helps you, please LIKE this comment/Kudos
- Anonymous
  Not applicable
  4 years ago
  Thank you this worked!

Related content