Get a single value per "group

steen_p — Thu, 16 Jul 2020 13:40:44 GMT

Hi,

I have writen a DAX query, that I'm using as a dataset in a SSRS Report, but I'm having troubles figuring out how to "identify" or get a only once from the returned dataset.

The query looks like this -

DEFINE
VAR vCurrentDate = "06-07-2020"-
VAR vReportDate = CALCULATE ( MAX ( 'Date'[Date]), 'Date'[Date] = DateValue(vCurrentDate)-1 )
VAR vReportMonthYear =CALCULATE ( MAX ( 'Date'[Month-Year] ), 'Date'[Date] = vReportDate )

EVALUATE
SUMMARIZECOLUMNS (
'Route'[Route Name],
'Product'[Product Name],
'Date'[Date],
FILTER ( VALUES ( 'Date'[Month-Year] ), ( 'Date'[Month-Year] = vReportMonthYear ) ),
"Quantity1", [Quantity1],
"Quantity2", [Quantity2]
)
ORDER BY 'Date'[Date] ,'Route'[Route Name]

(Actually it's more complex than this but this should be enough to illustrate the issue)

and the (simplified) dataset I get back is this -

Route Product Date Quantity1 Quantity2
X CC 01-07-2020 581 18
X CA 01-07-2020 558 18
X BU 01-07-2020 2 18
X PA 01-07-2020 18
Y CC 01-07-2020 2317 80
Y FPA 01-07-2020 80
Y BU 01-07-2020 2 80
Y CA 01-07-2020 1840 80

My problem, is then that I have a Matrix in my SSRS Report ,where I need to show the sum of Quantity2 for the month, but this gives me a too high a number because the same value apperas several times for each day/Route and not only once. I think I need either a sort of a "ROWNUMBER" for each record per day/Route so I can select only the one with e.g. RowNumber 1 or a way to extend my query, so I get a new row with only the value of Quantity2 as a new column , that I can sum.

I have been around many ideas about RANKX, creating rownumbers using EARLIER function etc, but no of it really seems to be work so now I'm a little bit out if ideas.

Does anyone of you have som ideas on how to solve the problem?

Regards

Steen

Re: Get a single value per "group

Anonymous — Thu, 16 Jul 2020 14:06:05 GMT

Not enough details to tell you how to solve this.

Best
D

Re: Get a single value per "group

steen_p — Thu, 16 Jul 2020 14:28:15 GMT

hmmm...maybe not, but which details do you need then?

I'm just looking for some tips and ideas that I can work on - not neccessarily a fixed solution.

topic Re: Get a single value per "group in DAX Commands and Tips

Get a single value per "group

Re: Get a single value per "group

Re: Get a single value per "group