Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |