topic Re: Use slicer value in Dax Measure in DAX Commands and Tips

Use slicer value in Dax Measure

Saskwyt — Wed, 20 Sep 2023 10:01:08 GMT

Hi there,

Please see attached a basic PBIX model showing a sales table, a budget table and a date table. I'm trying to work out how to adjust the DAX for the measures All_Sales and All_Budget.

Sales vs Budget.pbix

The measures are currently as follows:

All_Sales =

Calculate(

SUM(Tbl_Sales[Sales]),

Filter(Date_Table,Date_Table[Date] <= Date(2023,6,30))

)

All_Budget =

Calculate(

SUM(Tbl_Budget[Budget]),

Filter(Date_Table,Date_Table[EOM] > Date(2023,6,30))

I'm trying to change the DAX so that the "Date(2023,6,30)" is replaced by the value I choose in my Date slicer.

Can someone help me achieve this please?

Thanks in advance

Re: Use slicer value in Dax Measure

mlsx4 — Wed, 20 Sep 2023 10:21:08 GMT

Hi Saskwyt

I cannot see the pbix, but you can do:

All_Sales = var _max = maxx(ALLSELECTED('Date_Table'),'Date_Table'[Date]) RETURN CALCULATE(SUM(Tbl_Sales[Sales], FILTER(ALL('Date_Table'),'Date_Table'[Date]<=_max))

Same for budget...

Re: Use slicer value in Dax Measure

Saskwyt — Wed, 20 Sep 2023 10:31:52 GMT

Hey mlsx4 thanks for the feedback. See below an image of the table I'm trying to achieve - This is what my current Measures are returning. It looks like your suggested solution gives the total up to the date but doesn't show it month by month as shown below:

Hope that makes more sense!

Re: Use slicer value in Dax Measure

mlsx4 — Wed, 20 Sep 2023 10:54:02 GMT

Hi Saskwyt

I'm afraid then you will need to create some kind of a flag. I will give you a thread similar of what I'm thinking about: https://community.fabric.microsoft.com/t5/Desktop/Date-flag-based-on-slicer-selection-and-Dynamic-measure-values/m-p/1485968#M617448

Re: Use slicer value in Dax Measure

ChiragGarg2512 — Wed, 20 Sep 2023 12:05:37 GMT

Saskwyt , The function selectedvalue should help in this condition.

All_Sales =

Calculate(

SUM(Tbl_Sales[Sales]),

Date_Table[Date] <= SELECTEDVALUE(Date_Table[Date])
)

This should take the sum of sales till the date on slicer.

Re: Use slicer value in Dax Measure

Saskwyt — Wed, 20 Sep 2023 12:02:49 GMT

Hey mlsx4 I've managed to apply the information in the link to achieve what I was looking for, I've even managed to get a total column! There is one minor issue....the totals on the table are not correct 😞

The measure I've used for Sales_£ is as follows:

Sales_£ = VAR date1 = SELECTEDVALUE ( 'Table'[EOM] ) VAR date2 = SELECTEDVALUE ( 'Calendar'[EOM] ) RETURN IF ( date2 <= date1, Sum(Tbl_Sales[Sales]), 0 )

Hope the above makes sense!!

Re: Use slicer value in Dax Measure

Saskwyt — Wed, 20 Sep 2023 12:37:56 GMT

Hi ChiragGarg2512 I can't seem to get this to work...Thank you for your response though 🙂