topic Re: How to use different measures based on selected date range in DAX Commands and Tips

How to use different measures based on selected date range

Anonymous — Tue, 28 Mar 2023 08:18:47 GMT

Good Day

I have been struggling for weeks now. I need help please on using dax, I have 4 measures one for each quarter in the year. I am struggling to figure out how can One use the a certian measure based on the selected date range. eg: If user selects 01 Jan 2023-15 Jan 2023 measure 1 should apply for that period or the user selects 01 April 2023 - 15 April 2023 measure 2 should apply.

I would really appreciate the assistance.

Regards,

Itu

Re: How to use different measures based on selected date range

tt_and — Wed, 29 Mar 2023 08:09:08 GMT

Hi. I assume you have a date table that's used for slicing on dates. If you add a column to the date table defining each period on each date row, you can use this with a switch in a base measure.

Base measure = switch( calendar[Period], 1, [Measure1], 2, [Measure2], 3, [Measure3], 4, [Measure4] )

Re: How to use different measures based on selected date range

Anonymous — Mon, 03 Apr 2023 11:49:56 GMT

Good Day tt_and

Thank you for assisting. Kindly note that the switch formula doesn't return the calandar table, screen dump below to show what I am getting.

Re: How to use different measures based on selected date range

ferial_coder — Mon, 09 Sep 2024 18:21:52 GMT

Building on the approach proposed by tt_and, you can also create a measure. The trick is to utilize a vartual table within the measure to generate the total of all the different measures you are switching between.

Base measure = VAR __table1 = ADDCOLUMNS ( calendar, "@composite_measure", SWITCH ( calendar[Period], 1, [Measure1], 2, [Measure2], 3, [Measure3], 4, [Measure4] ) ) RETURN SUMX ( __table1, [@composite_measure] )