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
