topic Measure Using "Or" in a table to Count Rows with multiple qualifiers in DAX Commands and Tips

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

Matt0515 — Fri, 24 Apr 2020 17:37:40 GMT

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?

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

vivran22 — Sat, 25 Apr 2020 03:11:40 GMT

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

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

deevaker — Sat, 25 Apr 2020 06:55:33 GMT

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")
))