Selected Column Based on Slicer

[n8izzle]

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:

InvoiceDate	OrderDate	InvoiceAmount	OrderAmount	SelectedDate
12/15/2022	12/01/2022	13.50	26.50
12/12/2022	04/01/2022	28.50	53.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!

[bolfri]

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]

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 · Accepted solution]

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"