Filter on two columns

[Dayna]

Hello All,

I have a measure in my report where I want to do the following:

Count of all servers (ServerName) where there is a date entered within 'DateDrAvailable' and the flag for 'DisasterRecoveryAvailable' is true.

This will then give me a cumulative count for all servers that have a DR flag, and a count for when they were added based on the date available.

Originally, my calculation to give the cumulative count looked like this:

CumulCountAddedToDr = 
CALCULATE (
    COUNT ( 'DR Server'[ServerName] ),
    FILTER (
        ALLSELECTED ( 'DR Server'),
        'DR Server'[DateDrAvailable] <= MAX ( MasterCalendar[Date]) 
    )
)

This seemed to work, as the count increased as a server was added over time, but the number isn't accurate. I thought the best way to address this is to add another expression into the filter to check the DR Available flag is true. But I can't get the right syntax? 

This is what I've tried:

CumulCountAddedToDr = 
CALCULATE (
    COUNT ( 'DR Server'[ServerName] ),
    FILTER (
        ALLSELECTED ( 'DR Server'),
        'DR Server'[DateDrAvailable] <= MAX ( MasterCalendar[Date]) & 'DR Server'[DisasterRecoveryAvailable] = TRUE()
    ))

But this gives an error:

MdxScript(Model) (27, 9) Calculation error in measure 'MeasureValues'[CumulCountAddedToDr]: DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

Can someone help me, please?

Many thanks,

Dayna

[AlB]

Hi @Dayna

You could try doing what the error message suggests. You are comparing a value in date format with a value in text format. I assume the MasterCalendar[Date] is in date format so 'DR Server'[DateDrAvailable] must be text. Convert this column to date and try again.

[Dayna]

Good Morning @AlB,

Thanks for the below, I'm fairly new to PowerBI - is this best done within the data query and convert the column type, or within the measure. If the measure, am I right in thinking it is:

format('DR Server'[DateDrAvailable],"DD/MM/YYYY" )

Doing this removes the error, but I don't think it's made the change to the expression that I'd hope for. Is this the right way to pass in two different filters into the expression?

CumulCountAddedToDr = 
CALCULATE (
    COUNT ( 'DR Server'[ServerName] ),
    FILTER (
        ALLSELECTED ( 'DR Server'),
        'DR Server'[DisasterRecoveryAvailable] = TRUE() & (format('DR Server'[DateDrAvailable],"DD/MM/YYYY") <= format(MAX ( MasterCalendar[Date]),"DD/MM/YYYY"))
    ))

Many thanks,

Dayna

[AlB · Accepted solution]

Hi  @Dayna

Yes, It's easier to do it in the query editor, or directly on the table; you can change the format in both ways. I did not mean to do it through the FORMAT function.

The code you are showing now uses &.  Be careful because that is the concatenation operator. The logical AND, which is what you need here, is  &&

So to start with, I'd update that:

CumulCountAddedToDr = 
CALCULATE (
    COUNT ( 'DR Server'[ServerName] ),
    FILTER (
        ALLSELECTED ( 'DR Server'),
        'DR Server'[DisasterRecoveryAvailable] = TRUE() && (format('DR Server'[DateDrAvailable],"DD/MM/YYYY") <= format(MAX ( MasterCalendar[Date]),"DD/MM/YYYY"))
    ))

That might have actually caused the initial error since I am seeing that you didn't get the same error with the code that you show at the very beginning, and hat has the exact same comparison as the code you say throws the error (in blue below)

CumulCountAddedToDr = 
CALCULATE (
    COUNT ( 'DR Server'[ServerName] ),
    FILTER (
        ALLSELECTED ( 'DR Server'),
        'DR Server'[DateDrAvailable] <= MAX ( MasterCalendar[Date]) 
    )
) 

Was 'DR Server'[DateDrAvailable] already of type date? I suspect so

[Dayna]

Hi @AlB,

It's the ampersands, thank you!

(It was already of type date).

Kind Regards,

Dayna