topic Re: Conditional Measure using slicer selection in DAX Commands and Tips

Conditional Measure using slicer selection

Rob92 — Wed, 14 Aug 2024 14:48:23 GMT

I am trying to put together a stacked column chart, using the example table below, I want to show total transactions per month. The one color of the stacked column will show transactions by clients who joined before the start of the selected date range (marked those in bold below), and the other color of the bar will be transactions by clients who joined after the start of the selected date range (marked those in italics below). The user needs the ability to chose the date range with a slice. In this example let's say the user has chosen 1/1/24 - 3/31/24. So the Jan column would show a total of 18 transactions (16 from old clients, 2 from new), Feb would show 24 transactions (14 from old clients, 10 from new), and Mar would show 33 transactions (13 from old clients, 20 from new).

If the user changes the date range, the clients that are considered old or new will change. Here if they changed the range to 2/1/24 - 3/31/24, then client C would change from the new catagory to old.

Since the user needs control of the date range, I can't code these catagories into the datasource. I know I can't reference a single slicer value in a calculated column formula. So I'm trying to figure out if I can achieve this with a measure, using SWITCH and SELECTEDVALUE to check if a Client_joined date is before or after the start of the date range the user sets, and catagorize the transactions in the visual dynamically that way. Any thoughts on this? Or any other ways I could achieve this result? Thanks in advance!

Client	Client_joined	Transactions	Transaction_month
A	5/5/2023	9	Jan-24
B	10/10/2023	7	Jan-24
C	1/20/2024	2	Jan-24
A	5/5/2023	8	Feb-24
B	10/10/2023	6	Feb-24
C	1/20/2024	7	Feb-24
D	2/15/2024	3	Feb-24
A	5/5/2023	8	Mar-24
B	10/10/2023	5	Mar-24
C	1/20/2024	6	Mar-24
D	2/15/2024	8	Mar-24
E	3/5/2024	6	Mar-24

Re: Conditional Measure using slicer selection

lbendlin — Wed, 14 Aug 2024 21:10:24 GMT

You need to use a disconnected table to feed your date range slicer, and then use measures to filter your visual.

Re: Conditional Measure using slicer selection

Anonymous — Thu, 15 Aug 2024 05:27:06 GMT

Hi Rob92 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a sperated date dimension table(DO NOT create any relationship with your fact table) just as suggested by Ibendlin

2. Create two measures as below

Old clients count = VAR _mindate = MIN ( 'Date'[Date] ) RETURN CALCULATE ( SUM ( 'Table'[Transactions] ), FILTER ( 'Table', 'Table'[Client_joined] <= _mindate ) )

New clients count = VAR _mindate = MIN ( 'Date'[Date] ) RETURN CALCULATE ( SUM ( 'Table'[Transactions] ), FILTER ( 'Table', 'Table'[Client_joined] > _mindate ) )

3. Create a table visual as shown in the below screenshot

Best Regards

Re: Conditional Measure using slicer selection

Rob92 — Thu, 15 Aug 2024 11:17:10 GMT

Awesome, thanks guys! I have implemented the solution on my real data and it works a treat. The only issue was that as well as the slicer controlling which users are new or old, I also need it to filter the visual. In order to do this I have joined the date dimension table to the fact table. This causes the date hierachy to break but that's not a problem - to resolve that, I added separate month and year columns in power query, and used those on the X axis instead. Problem solved!