topic Re: Measure to Group Data in DAX Commands and Tips

Measure to Group Data

Anonymous — Wed, 27 Nov 2019 14:53:42 GMT

Hi,

I am using a Tabular live connection, and I need to group my stock value into Seasons.

If it was imported data I would just add a column like below, but how do I get that to work in a measure?

IF(MONTH([Last Invoice Date])<7;"SS"&YEAR([Last Invoice Date]);"AW"&year([Last Invoice Date])))

Then I would use this measure as columns in a Matrix.

I hope it makes sense what I am asking for.

Thanks,

Re: Measure to Group Data

Greg_Deckler — Wed, 27 Nov 2019 15:09:56 GMT

You need to wrap an aggregation function around your columns like MAX, MIN, SUM, FIRSTNONBLANK, LASTNONBLANK, AVERAGE, etc.

Re: Measure to Group Data

Anonymous — Thu, 28 Nov 2019 07:10:49 GMT

Hi,

It does not work. It only returns AW19. Furthermore, I can only add this to values, I need it in Columns of my Matrix.

Season = MAX([Last Invoice Date];IF(MONTH([Last Invoice Date])<7;"SS"&YEAR([Last Invoice Date]);"AW"&year([Last Invoice Date])))

I need to create below Matrix.

I do not know if it matters that Last Invoice Date is a measure.

Thanks,

Re: Measure to Group Data

nhoward — Thu, 28 Nov 2019 22:29:57 GMT

Hi,

Measures are only meant for values.

Your seasons are dimensions. Assuming you have a Calendar Table, you need a column in there, that shows the Season that each day belongs to. You can then use that column as a column in your Matrix. It would work in a similar way to having Quarters in your calendar.

Hope that helps.

Re: Measure to Group Data

Anonymous — Tue, 03 Dec 2019 14:04:09 GMT

Hi nhoward,

It is in a live connection so I cannot do that. I need it to be a measure, I cannot add tables.

Maybe it is just not possble.

Re: Measure to Group Data

nhoward — Tue, 03 Dec 2019 22:24:32 GMT

I'm fairly sure that you can import from another source, even though most of your tables are live connections.

You do need a master calendar, if you need to group/summarise by periods.