Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dax filter syntax

Hello all

 

In DAX , how can i create a Measure that filter all values by a specific date for each value 

for example :

 I have a table with 3 columns : customer_id (PK) , country , register_date ( all data for all countries is from 1/1/2018 ) 

how can i create a parameter that filters the customer_id  by a diffrent and specific date for each a country  ?

show only customer_id from Spain that the  register_date is >= 2/14/2018

And

show only customer_id from Canada that the  register_date is >= 9/24/2018

And

show only customer_id from France that the  register_date is >= 7/30/2019

  • vivran22's avatar
    vivran22
    6 years ago

    Hello Anonymous,

     

    You may try this measure:

     

    Customer Count =
VAR _Spain =
    DATE ( 2018, 2, 14 )
VAR _Canada =
    DATE ( 2018, 9, 24 )
VAR _France =
    DATE ( 2019, 7, 30 )
RETURN
    CALCULATE (
        COUNT ( dtTable[CustomerID] ),
        FILTER (
            dtTable,
            dtTable[Country] = "Spain"
                && dtTable[RegisterDate] >= _Spain
        )
    )
        + CALCULATE (
            COUNT ( dtTable[CustomerID] ),
            FILTER (
                dtTable,
                dtTable[Country] = "Canada"
                    && dtTable[RegisterDate] >= _Canada
            )
        )
        + CALCULATE (
            COUNT ( dtTable[CustomerID] ),
            FILTER (
                dtTable,
                dtTable[Country] = "France"
                    && dtTable[RegisterDate] >= _France
            )
        )

     

    Then in the visual, you can add this as Filter = is not blank:

     

    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

2 Replies

  • vivran22's avatar
    vivran22
    Community Champion
    6 years ago

    Hello Anonymous,

     

    You may try this measure:

     

    Customer Count =
VAR _Spain =
    DATE ( 2018, 2, 14 )
VAR _Canada =
    DATE ( 2018, 9, 24 )
VAR _France =
    DATE ( 2019, 7, 30 )
RETURN
    CALCULATE (
        COUNT ( dtTable[CustomerID] ),
        FILTER (
            dtTable,
            dtTable[Country] = "Spain"
                && dtTable[RegisterDate] >= _Spain
        )
    )
        + CALCULATE (
            COUNT ( dtTable[CustomerID] ),
            FILTER (
                dtTable,
                dtTable[Country] = "Canada"
                    && dtTable[RegisterDate] >= _Canada
            )
        )
        + CALCULATE (
            COUNT ( dtTable[CustomerID] ),
            FILTER (
                dtTable,
                dtTable[Country] = "France"
                    && dtTable[RegisterDate] >= _France
            )
        )

     

    Then in the visual, you can add this as Filter = is not blank:

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you very much vivran22  ! it helped a lot !