Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

DAX - Column content based on slicer selection

Hi folks,

I've done some research on the forum and while some responses get close to what I need I can't quite find something that I can use.

 

I have a table which contains, amongst many fields, the following:

ITEM #      REQ CREATION DATE     PO CREATION DATE     RECEIVED DATE     REFERENCE DATE

 

I would like to have a filter to select either REQ CREATION DATE or PO CREATION DATE. If I select REQ CREATION DATE, then the column REFERENCE DATE needs to show the value across in REQ CREATION DATE. If I select PO CREATION DATE, the REFERENCE DATE should display the value across PO CREATION DATE.

 

I feel like this is not rocket science but cannot for the like of me do it :D

 

Thanks in advance to anyone who can help.

OF

 

Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    Hi Anonymous,

     

    Did you had a measure or a calculated column?

     

    I'm assuming that since your data is always the same in the sample you showned that you created a calculated column, this will give you the maximum value of the date column in your data.

     

    Below you can see the same measure as you have but in a calculated colum (COLUMN_REF) and in a measure (MEASURE_REF) the only one that changes accordingly to the slicer is the measure.

     

     

    You need to change your formula to a measure instead of the column.

     

    Regards,

    MFelix

     

16 Replies

  • MFelix's avatar
    MFelix
    Super User
    8 years ago

    Hi Anonymous,

     

    When you say that you need to have one date or the other is to have calculations made to show in a visual?

     

    What is the final outcome you want? Because based on that can be different options.

     

    However one of the options is to create a table with two lines with:

    REQ CREATION DATE

    PO CREATION DATE

     

    Then add the following measure to your model:

     

     

    REFERENCE DATE = 
SWITCH (
    TRUE ();
    MAX ( Slicer_table[Slicer] ) = "REQ CREATION DATE"; MAX ('DataTable'[REQ CREATION DATE]  );
    MAX ( Slicer_table[Slicer] ) = "PO CREATION DATE"; MAX ( 'DataTable'[PO CREATION DATE] );
    MAX ( 'DataTable'[REQ CREATION DATE] )
)

     

    This can then be used in another measures or visuals:

     

    Regards,

    MFelix

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago
      Hi MFeliz,

      I will test this as soon as I’m back at work tomorrow :) to answer your question, I would like to reference the value in REFERENCE DATE in several formulas and have a result dependant upon the filter.

      Do you think that this solution will enable this?

      Thanks,
      OF
      • MFelix's avatar
        MFelix
        Super User
        8 years ago

        Hi Anonymous,

         

        If you reference to this measure on the other formulas this will give you the expected result.

         

        But be aware that using a measure is based on context so some of the formulas are not just simply using the measure you need to use aggregators or some filters in the new measures.

         

        Regards,

        MFelix