Measure Using "Or" in a table to Count Rows with multiple qualifiers

[Matt0515]

Hello,

I am struggling to write a DAX Calculation to report the number of records that qualify based on multiple components.

I have tried to use the "OR" function several different ways and the only one I can get to work provides me with the incorrect count. (code below)

I know I should have 19 qualified lines using this logic.

     Opportunity Solution = X and Created Date Year = 2020

Plus

     Opportunity Solution = Y and Created Date Year = 2020

However, based on the below code I receive the count of 63.

2020 TMS/WS MKT MQL2 =

CALCULATE (

    COUNTROWS ( Raw_Data ),

    FILTER (

        ALLSELECTED ( Raw_Data ),

        OR(Raw_Data[Opportunity Solution] = "X"

            && YEAR ( Raw_Data[Created Date] ) = 2020,

        Raw_Data[Opportunity Solution] = "Y"

            && YEAR ( Raw_Data[Created Date] ) = 2020

    )

)))

Can someone please help me understand how to write the measure that will result in the total of lines that

meet both requirements?

[deevaker · Accepted solution]

Try this one :

2020 TMS/WS MKT MQL2 =
CALCULATE (
    COUNTROWS ( Raw_Data ),
    FILTER (
        ALLSELECTED ( Raw_Data ),
YEAR ( Raw_Data[Created Date] ) = 2020 &&
(Raw_Data[Opportunity Solution] = "X" || Raw_Data[Opportunity Solution] = "Y")
))

[vivran22]

Hello @Matt0515 ,

Try this:

Measure 2020 = 

VAR _Filter2020 = 
    FILTER('Table',
        'Table'[Created Date Year] = 2020
        )

VAR _Count = 
    CALCULATE(
        COUNTROWS('Table'),
        _Filter2020,
        'Table'[Opportunity Solution] = "X" || 'Table'[Opportunity Solution] = "Y"
    )

RETURN

_Count

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn