Forum Discussion

Dayna's avatar
Dayna
Icon for Helper V rankHelper V
7 years ago
Solved

Filter on two columns

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 'DisasterR...
Need Help
  • AlB's avatar
    AlB
    7 years ago

    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's avatar
Dayna
Icon for Helper V rankHelper V
7 years ago

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's avatar
AlB
Icon for Community Champion rankCommunity Champion
7 years ago

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's avatar
    Dayna
    Icon for Helper V rankHelper V
    7 years ago

    Hi AlB,

     

    It's the ampersands, thank you!

     

    (It was already of type date).

     

    Kind Regards,

    Dayna