See the following dax query gererated from BI for use with a paginated report.
I cannot work out the syntax to remove the TOPN (501, restriction. there are abot 2500 records in this data set currently.
if i remove all this block of code:
VAR __DS0PrimaryWindowed =
TOPN(
501,
__DS0PrimaryShowAll,
'Survey'[UR],
1,
'Survey'[Action Category],
1,
'Survey'[ApptID],
1,
'Survey'[AppointmentModality],
1,
'Survey'[Created],
1,
'Survey'[Feedback],
1,
'Survey'[ExamCode],
1,
'Survey'[Firstname],
1,
'Survey'[appoint Date],
1,
'Survey'[Status],
1,
'Survey'[Surname],
1,
'Survey'[ThemeOfFeedback],
1,
'Survey Responsibilities'[Responsibility],
1,
'Survey'[create date],
1
)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
'Survey'[UR],
'Survey'[Action Category],
'Survey'[ApptID],
'Survey'[AppointmentModality],
'Survey'[Created],
'Survey'[Feedback],
'Survey'[ExamCode],
'Survey'[Firstname],
'Survey'[appoint Date],
'Survey'[Status],
'Survey'[Surname],
'Survey'[ThemeOfFeedback],
'Survey Responsibilities'[Responsibility],
'Survey'[create date]
i get a error "the end of the input was reached"
this is the full code:
// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({"MRI"}, 'Survey Responsibilities'[Responsibility])
VAR __DS0Core =
SELECTCOLUMNS(
KEEPFILTERS(
FILTER(
KEEPFILTERS(
SUMMARIZECOLUMNS(
'Survey'[UR],
'Survey'[Action Category],
'Survey'[ApptID],
'Survey'[AppointmentModality],
'Survey'[Created],
'Survey'[Feedback],
'Survey'[ExamCode],
'Survey'[Firstname],
'Survey'[appoint Date],
'Survey'[Status],
'Survey'[Surname],
'Survey'[ThemeOfFeedback],
'Survey Responsibilities'[Responsibility],
'Survey'[create date],
__DS0FilterTable,
"CountRowsRadiology_Survey_Responsibilities", CALCULATE(COUNTROWS('Survey Responsibilities'))
)
),
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
NOT(ISBLANK('Survey'[UR])),
NOT(ISBLANK('Survey'[Action Category]))
),
NOT(ISBLANK('Survey'[ApptID]))
),
NOT(ISBLANK('Survey'[AppointmentModality]))
),
NOT(ISBLANK('Survey'[Created]))
),
NOT(ISBLANK('Survey'[Feedback]))
),
NOT(ISBLANK('Survey'[ExamCode]))
),
NOT(ISBLANK('Survey'[Firstname]))
),
NOT(ISBLANK('Survey'[appoint Date]))
),
NOT(ISBLANK('Survey'[Status]))
),
NOT(ISBLANK('Survey'[Surname]))
),
NOT(ISBLANK('Survey'[ThemeOfFeedback]))
),
NOT(ISBLANK('Survey Responsibilities'[Responsibility]))
),
NOT(ISBLANK('Survey'[create date]))
)
)
),
"'Survey'[UR]", 'Survey'[UR],
"'Survey'[Action Category]", 'Survey'[Action Category],
"'Survey'[ApptID]", 'Survey'[ApptID],
"'Survey'[AppointmentModality]", 'Survey'[AppointmentModality],
"'Survey'[Created]", 'Survey'[Created],
"'Survey'[Feedback]", 'Survey'[Feedback],
"'Survey'[ExamCode]", 'Survey'[ExamCode],
"'Survey'[Firstname]", 'Survey'[Firstname],
"'Survey'[appoint Date]", 'Survey'[appoint Date],
"'Survey'[Status]", 'Survey'[Status],
"'Survey'[Surname]", 'Survey'[Surname],
"'Survey'[ThemeOfFeedback]", 'Survey'[ThemeOfFeedback],
"'Survey Responsibilities'[Responsibility]", 'Survey Responsibilities'[Responsibility],
"'Survey'[create date]", 'Survey'[create date]
)
VAR __DS0PrimaryShowAllCompat =
CALCULATETABLE(
FILTER(
KEEPFILTERS(
GENERATEALL(
KEEPFILTERS(VALUES('Survey'[UR])),
CALCULATETABLE(
GENERATEALL(
KEEPFILTERS(VALUES('Survey'[Action Category])),
CALCULATETABLE(
GENERATEALL(
KEEPFILTERS(VALUES('Survey'[ApptID])),
CALCULATETABLE(
GENERATEALL(
KEEPFILTERS(VALUES('Survey'[AppointmentModality])),
CALCULATETABLE(
GENERATEALL(
KEEPFILTERS(VALUES('Survey'[Created])),
CALCULATETABLE(
GENERATEALL(
KEEPFILTERS(VALUES('Survey'[Feedback])),
CALCULATETABLE(
GENERATEALL(
KEEPFILTERS(VALUES('Survey'[ExamCode])),
CALCULATETABLE(
GENERATEALL(
KEEPFILTERS(VALUES('Survey'[Firstname])),
CALCULATETABLE(
GENERATEALL(
KEEPFILTERS(VALUES('Survey'[appoint Date])),
CALCULATETABLE(
GENERATEALL(
KEEPFILTERS(VALUES('Survey'[Status])),
CALCULATETABLE(
GENERATEALL(
KEEPFILTERS(VALUES('Survey'[Surname])),
CALCULATETABLE(
GENERATEALL(
KEEPFILTERS(VALUES('Survey'[ThemeOfFeedback])),
CALCULATETABLE(
GENERATEALL(
KEEPFILTERS(
FILTER(
KEEPFILTERS(VALUES('Survey Responsibilities'[Responsibility])),
CALCULATE(NOT(ISEMPTY('Survey Responsibilities')))
)
),
CALCULATETABLE(
FILTER(
KEEPFILTERS(VALUES('Survey'[create date])),
CALCULATE(NOT(ISEMPTY('Survey Responsibilities')))
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
)
),
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
OR(
NOT(ISBLANK('Survey'[UR])),
NOT(ISBLANK('Survey'[Action Category]))
),
NOT(ISBLANK('Survey'[ApptID]))
),
NOT(ISBLANK('Survey'[AppointmentModality]))
),
NOT(ISBLANK('Survey'[Created]))
),
NOT(ISBLANK('Survey'[Feedback]))
),
NOT(ISBLANK('Survey'[ExamCode]))
),
NOT(ISBLANK('Survey'[Firstname]))
),
NOT(ISBLANK('Survey'[appoint Date]))
),
NOT(ISBLANK('Survey'[Status]))
),
NOT(ISBLANK('Survey'[Surname]))
),
NOT(ISBLANK('Survey'[ThemeOfFeedback]))
),
NOT(ISBLANK('Survey Responsibilities'[Responsibility]))
),
NOT(ISBLANK('Survey'[create date]))
)
),
KEEPFILTERS(__DS0FilterTable)
)
VAR __DS0PrimaryShowAll =
SUMMARIZE(
UNION(__DS0Core, __DS0PrimaryShowAllCompat),
'Survey'[UR],
'Survey'[Action Category],
'Survey'[ApptID],
'Survey'[AppointmentModality],
'Survey'[Created],
'Survey'[Feedback],
'Survey'[ExamCode],
'Survey'[Firstname],
'Survey'[appoint Date],
'Survey'[Status],
'Survey'[Surname],
'Survey'[ThemeOfFeedback],
'Survey Responsibilities'[Responsibility],
'Survey'[create date]
)
VAR __DS0PrimaryWindowed =
TOPN(
501,
__DS0PrimaryShowAll,
'Survey'[UR],
1,
'Survey'[Action Category],
1,
'Survey'[ApptID],
1,
'Survey'[AppointmentModality],
1,
'Survey'[Created],
1,
'Survey'[Feedback],
1,
'Survey'[ExamCode],
1,
'Survey'[Firstname],
1,
'Survey'[appoint Date],
1,
'Survey'[Status],
1,
'Survey'[Surname],
1,
'Survey'[ThemeOfFeedback],
1,
'Survey Responsibilities'[Responsibility],
1,
'Survey'[create date],
1
)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
'Survey'[UR],
'Survey'[Action Category],
'Survey'[ApptID],
'Survey'[AppointmentModality],
'Survey'[Created],
'Survey'[Feedback],
'Survey'[ExamCode],
'Survey'[Firstname],
'Survey'[appoint Date],
'Survey'[Status],
'Survey'[Surname],
'Survey'[ThemeOfFeedback],
'Survey Responsibilities'[Responsibility],
'Survey'[create date]
