DAX Calculate Measure with IF Statement-CodePudding

I am trying to count the number of codes {"1V", "2V", "3V"} contained in the [ToStatus] and [FromStatus] column of the' Master Report' table. However, I need only count the code once if it appears in both columns.

I am relatively new to DAX and the below is my attempt at coding same. I would be obliged if any of the gurus out there could be of assistance :)

Psuedo code: If any of the values {"1V", "2V", "3V"} is in column [ToStatus] and [FromStatus] Just count [ToStatus] else count [FromStatus] and return the total

Attempt in DAX:

=IF(('Master Report'[ToStatus] IN {"1V", "2V", "3V"} && 'Master Report'[FromStatus] IN {"1V", "2V", "3V"}),

CALCULATE([_PatientCount],
FILTER('Master Report',
'Master Report'[ToStatus] IN {"1V", "2V", "3V"}),

CALCULATE([_PatientCount],
FILTER('Master Report',
'Master Report'[FromStatus] IN {"1V", "2V", "3V"} ||
'Master Report'[ToStatus] IN {"1V", "2V", "3V"})
)

Sample Data:

ID Code FromStatus ToStatus

145320150978 3V 2V
145320150978 3V 2V
145320210617 RH 3V
145320210617 2V ZO
145320210628 3C 2V
145320210628 2V 5L
145320211246 3V 5S

Update: Also I forgot to mention that the purpose of the _PatientCode measure is to count DISTINCT patient codes!

Update: I have now resolved this by doing it in MySQL instead of DAX and then just counting the 1 values in DAX.

Thanks in advance, Conor.

CodePudding user response：

Some brackets were missing, others were superfluous:

MyMeasure =
IF (
    'Master Report'[ToStatus] IN { "1V", "2V", "3V" }
        && 'Master Report'[FromStatus] IN { "1V", "2V", "3V" },
    CALCULATE (
        [_PatientCount],
        FILTER ( 
            'Master Report', 
            'Master Report'[ToStatus] IN { "1V", "2V", "3V" } )
    ),
    CALCULATE (
        [_PatientCount],
        FILTER (
            'Master Report',
            'Master Report'[FromStatus] IN { "1V", "2V", "3V" }
                || 'Master Report'[ToStatus] IN { "1V", "2V", "3V" }
        )
    )
)

CodePudding user response：

Resolved from MySQL side with code below:

(CASE WHEN ToStatus LIKE '%V' OR FromStatus LIKE '%V' THEN 1 ELSE 0 END) as V_Count