Forum Discussion

Yael's avatar
Yael
Icon for Helper IV rankHelper IV
8 years ago

DAX - max function that takes filters into consideration dynamically

Hello,

 

Need help with DAX function please.

Below is a pic of my data source, I'm interested in populating the field "Is Maximum Date" with 1 / 0 values.

Row NumberDateTransaction NumberCustomerCustomer CategoryIs Maximum Date
16-Mar-18100A-JohnA 
26-Mar-18200B-KimB 
37-Mar-18300A-JohnA 
47-Mar-18400A-JohnA 

I want that field to be calculated according to filters applied by the user -

If user select in filter Customer = "A-John", I want to see Is Maximum Date =1 for row 3 and 4

If user select in filter Customer Category = "B", I want to see Is Maximum Date =1 for row 2

and so on...

How can I do this please?

 

Thanks!

Need Help

8 Replies

  • GeorgeBuster's avatar
    GeorgeBuster
    Icon for Advocate III rankAdvocate III
    8 years ago

    Hi Yael,

     

    On the one hand, you can't create calculated columns depending on report filters because the values of these columns are calculated when the data is loaded before the user select the filter.


    On the other hand, if you want to fill your column with 1/0, where 1 is once per user we can do it:


    Is Maximum Date =
    VAR CUSTOMER = MAX(Customer)
    RETURN
    IF(
        Date = CALCULATE(
                    MAX(Date);
                    ALL(TransactionsTable);
                    Customer = CUSTOMER
               );
        1;
        0
    )

     

    Hope this can help you,

    Jorge.

    • Yael's avatar
      Yael
      Icon for Helper IV rankHelper IV
      8 years ago

      Thanks GeorgeBuster! the above DAX ereturns an error message, not specifing where is the issue...

      • GeorgeBuster's avatar
        GeorgeBuster
        Icon for Advocate III rankAdvocate III
        8 years ago

        Could you share a screenshot? Maybe we can find the error. Thanks.

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Yael,

     

    Please check out a demo here.

    1. You need dimensional tables in your scenario.

    2. Two slicers.

    3. The measure could be like this:

    Measure =
VAR maxdate =
    CALCULATE ( MAX ( 'Table1'[Date] ), ALL ( 'Table1'[Row Number] ) )
RETURN
    IF ( MIN ( 'Table1'[Date] ) = maxdate, 1, 0 )

    DAX_max_function_that_takes_filters_into_consideration_dynamically

     

    Best Regards,

    Dale

    • Yael's avatar
      Yael
      Icon for Helper IV rankHelper IV
      8 years ago

      Thanks v-jiascu-msft. Tried creating the measure in the DateDim:

      IsMaxDate =
      VAR maxdate =
      CALCULATE (MAX(dim[Date]), ALL(dim[DatePK]))
      RETURN
      IF (MAX(dim[Date]) = maxdate, 1, 0 )

      When I drag this measure to my fact table, I see that it's duplicating the rows for dates that don't exist in the fact + all rows are getting 1 in "IsMaxDate"

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

        HI Yael

         

        Try this one

         

        IsMaxDate =
VAR maxdate =
    CALCULATE ( MAX ( dim[Date] ), ALLSELECTED () )
RETURN
    IF ( MAX ( dim[Date] ) = maxdate, 1, 0 )