Paginated Report Power BI

[ahmadibrahimbus]

How can i use addmissingitem with the below dax in paginated report query.

EVALUATE SUMMARIZECOLUMNS('Project_Dates'[WE_Date], 'EWP Overall'[Status_order], 'EWP_Status_Colors'[fgColor], 'EWP_Status_Colors'[bgColor], 'Projects'[project_name], RSCustomDaxFilter(@Projectsprojectid,EqualToCondition,[Projects].[project_id],String), RSCustomDaxFilter(@Projectsprojectname,EqualToCondition,[Projects].[project_name],String), RSCustomDaxFilter(@EWPOverallAreafromEWP,EqualToCondition,[EWP Overall].[Area from EWP],String), RSCustomDaxFilter(@CWPOverAllCriticalityNew,EqualToCondition,[CWP_Over_All].[Criticality_New],String), RSCustomDaxFilter(@Query1TodayDate,EqualToCondition,[Query1].[Today Date],String), FILTER(VALUES('Project_Dates'[Dates Slicer]), ('Project_Dates'[Dates Slicer] = "Focus")), "count_Status_order", [count_Status_order])

Thanks for your help.

[OwenAuger · Accepted solution]

Hi @ahmadibrahimbus 

As a starting point you could try the below code.

This assumes that you want to use the five GroupBy columns as ShowAll/GroupBy columns in ADMISSINGITEMS.

(I've formatted with DAX Formatter)

EVALUATE
ADDMISSINGITEMS (
    'Project_Dates'[WE_Date],
    'EWP Overall'[Status_order],
    'EWP_Status_Colors'[fgColor],
    'EWP_Status_Colors'[bgColor],
    'Projects'[project_name],
    SUMMARIZECOLUMNS (
        'Project_Dates'[WE_Date],
        'EWP Overall'[Status_order],
        'EWP_Status_Colors'[fgColor],
        'EWP_Status_Colors'[bgColor],
        'Projects'[project_name],
        RSCUSTOMDAXFILTER (
            @Projectsprojectid,
            EqualToCondition,
            [Projects].[project_id],
            String
        ),
        RSCUSTOMDAXFILTER (
            @Projectsprojectname,
            EqualToCondition,
            [Projects].[project_name],
            String
        ),
        RSCUSTOMDAXFILTER (
            @EWPOverallAreafromEWP,
            EqualToCondition,
            [EWP Overall].[Area from EWP],
            String
        ),
        RSCUSTOMDAXFILTER (
            @CWPOverAllCriticalityNew,
            EqualToCondition,
            [CWP_Over_All].[Criticality_New],
            String
        ),
        RSCUSTOMDAXFILTER (
            @Query1TodayDate,
            EqualToCondition,
            [Query1].[Today Date],
            String
        ),
        FILTER (
            VALUES ( 'Project_Dates'[Dates Slicer] ),
            ( 'Project_Dates'[Dates Slicer] = "Focus" )
        ),
        "count_Status_order", [count_Status_order]
    ),
    'Project_Dates'[WE_Date],
    'EWP Overall'[Status_order],
    'EWP_Status_Colors'[fgColor],
    'EWP_Status_Colors'[bgColor],
    'Projects'[project_name]
)

Does this give you the expected result?

Regards,

Owen