Forum Discussion
Solved
Measure Using "Or" in a table to Count Rows with multiple qualifiers
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?
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")
))
2 Replies
- vivran22
Community Champion
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 _CountCheers!
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 - deevaker
Resolver I
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")
))
