Forum Discussion

n8izzle's avatar
n8izzle
Frequent Visitor
3 years ago
Solved

Selected Column Based on Slicer

Hello all,

I am trying to create a dynamic column that will house a transaction date and will vary dependant on a slicer (SelectedAttribute[Attribute]). Ideally, I want to select "Order" on the slicer which will then adjust the column values to match with the "OrderDate" column. Then, if the user selects "Invoice" on the slicer, the columns date would change and match with the "InvoiceDate" column. I have another similar column that will pull the Amount based on same slicer which seems to be working using similar code as shown below.

 

Below is an example of what the dataset looks like:

 

InvoiceDateOrderDateInvoiceAmountOrderAmountSelectedDate
12/15/202212/01/202213.5026.50 
12/12/202204/01/202228.5053.50 
 12/15/2022 18.75 

 

Code I tried but the date always goes to the default "InvoiceDate":

 

 

SelectedDate = IF(
                    SELECTEDVALUE(SelectedAttribute[Attribute])="Order", DATEVALUE(ItemDetail[OrderDate]),
                    IF(SELECTEDVALUE(SelectedAttribute[Attribute])="Invoice",DATEVALUE(ItemDetail[InvoiceDate]), 
                    DATEVALUE(ItemDetail[InvoiceDate])))

 

 

 

Thank you for your help and support!

Need Help
  • bolfri's avatar
    bolfri
    3 years ago

    1. Create new calendar table

    It holds all the date(s) from your data.

    dim_calendar = CALENDAR(MIN(FIRSTDATE('Sample'[InvoiceDate]);FIRSTDATE('Sample'[OrderDate]));MAX(LASTDATE('Sample'[InvoiceDate]);LASTDATE('Sample'[OrderDate]))) 

    2. Create relationship to OrderDate and InvoiceDate

     Note that Active relations is between Date and OrderDate and Inactive between Date and InvoiceDate.

    3. Create another table "Parameter" that holds an column with Invoice/Order value

    You can use this DAX code or create a table using copy and pase from excel or something

    Parameter = UNION( ROW("Type";"Invoice"); ROW("Type";"Order") )

    4. Create a measure in this table that holds an information abour user pick

    It means: show me what's picked in Parameter[Type] (if it's one value). If all of them or none are selected the default Type will be Order. It's recommended to use filter with single selection to avoid using exceptions.

    Selected Type = SELECTEDVALUE(Parameter[Type];"Order")

     

    Behaviour on this measure:

    So change it to single selection πŸ™‚

     

    5. Create a measure based on slicer

    This measure should contains an information about what to calculate in when Order/Invoice is selected.

    Measure based on slicer =
    SWITCH([Selected Type];
    "Invoice";SUM('Sample'[InvoiceAmount]);
    "Order";SUM('Sample'[OrderAmount])
    )

     

    You can see it in action right away in a card viz:

    6. Create a measure to stick to correct date

    Depending on user selection you are activating correct relationship.

    Measure to date =
    SWITCH([Selected Type];
    "Invoice";CALCULATE([Measure based on slicer];USERELATIONSHIP(dim_calendar[Date];'Sample'[InvoiceDate]));
    "Order";CALCULATE([Measure based on slicer];USERELATIONSHIP(dim_calendar[Date];'Sample'[OrderDate]))
    )

    7. You can viz it πŸ™‚

    REMEMBER TO USE A DATE FROM DIM_CALENDAR TABLE - not Order/Invoice Date

    I have also created a dynamic title measure to help you understand whats goin on:

    Dynamic title = "Sum of " & [Selected Type] & " amount by " & [Selected Type] & " date"

3 Replies

  • bolfri's avatar
    bolfri
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Try this one.

    1. Create new table

    Solution = 
UNION(
    SELECTCOLUMNS(FILTER('Sample';NOT(ISBLANK('Sample'[InvoiceDate])));"Date";'Sample'[InvoiceDate];"Amount";'Sample'[InvoiceAmount];"Type";"Invoice");
    SELECTCOLUMNS('Sample';"Date";'Sample'[OrderDate];"Amount";'Sample'[OrderAmount];"Type";"Order")
)

    2. Create new measure

    Amount Value = SUM(Solution[Amount])

    3. Viz it

     

    • n8izzle's avatar
      n8izzle
      Frequent Visitor
      3 years ago

      Hi bolfri , Thank you for your quick help! I tried this solution and it does work accordingly. However, I have many other columns on the main table and would like to try and keep it all on the same table.

       

      Do you know if this is possible? Thanks again!

      • bolfri's avatar
        bolfri
        Icon for Solution Sage rankSolution Sage
        3 years ago

        1. Create new calendar table

        It holds all the date(s) from your data.

        dim_calendar = CALENDAR(MIN(FIRSTDATE('Sample'[InvoiceDate]);FIRSTDATE('Sample'[OrderDate]));MAX(LASTDATE('Sample'[InvoiceDate]);LASTDATE('Sample'[OrderDate]))) 

        2. Create relationship to OrderDate and InvoiceDate

         Note that Active relations is between Date and OrderDate and Inactive between Date and InvoiceDate.

        3. Create another table "Parameter" that holds an column with Invoice/Order value

        You can use this DAX code or create a table using copy and pase from excel or something

        Parameter = UNION( ROW("Type";"Invoice"); ROW("Type";"Order") )

        4. Create a measure in this table that holds an information abour user pick

        It means: show me what's picked in Parameter[Type] (if it's one value). If all of them or none are selected the default Type will be Order. It's recommended to use filter with single selection to avoid using exceptions.

        Selected Type = SELECTEDVALUE(Parameter[Type];"Order")

         

        Behaviour on this measure:

        So change it to single selection πŸ™‚

         

        5. Create a measure based on slicer

        This measure should contains an information about what to calculate in when Order/Invoice is selected.

        Measure based on slicer =
        SWITCH([Selected Type];
        "Invoice";SUM('Sample'[InvoiceAmount]);
        "Order";SUM('Sample'[OrderAmount])
        )

         

        You can see it in action right away in a card viz:

        6. Create a measure to stick to correct date

        Depending on user selection you are activating correct relationship.

        Measure to date =
        SWITCH([Selected Type];
        "Invoice";CALCULATE([Measure based on slicer];USERELATIONSHIP(dim_calendar[Date];'Sample'[InvoiceDate]));
        "Order";CALCULATE([Measure based on slicer];USERELATIONSHIP(dim_calendar[Date];'Sample'[OrderDate]))
        )

        7. You can viz it πŸ™‚

        REMEMBER TO USE A DATE FROM DIM_CALENDAR TABLE - not Order/Invoice Date

        I have also created a dynamic title measure to help you understand whats goin on:

        Dynamic title = "Sum of " & [Selected Type] & " amount by " & [Selected Type] & " date"