Forum Discussion

Karolina411's avatar
Karolina411
Helper V
2 years ago
Solved

parameter duplicates in Report Builder for Paginated Reports.

I have a Paginated Report (I am using a tabular model from our analysis server) question as I have 2 datasets joined by a common Parameter which is the DateColumn MonthYear- I have these referenced inside both datasets like so: (dataset1 uses both parameters for filtering but I want dataset2 to only use the date filter)

Dataset1 has YearMonth and PhysicianOrg parameters but Dataset2 only has YearMonth as we do not need the PhysicianOrg parameter for Dataset2.  I have a total of 3 datasets from the same source.

 

My Parameter ‘physician organization’ is creating duplicates in the slicer when I view the report.  Any way to solve this?  Here is the dax and and the values are from the 2nd table but the parameter is referenced in DataSet1.

EVALUATE SUMMARIZECOLUMNS('DateDim'[YearMonth],

'Providers'[PhysicianOrganization])

 

 

 

 

 

 

 

"Report Builder"
Dax Duplicates
  • aduguid's avatar
    aduguid
    2 years ago

    Try this as your dataset2

     

    EVALUATE 
SUMMARIZECOLUMNS(
    'Providers'[PhysicianOrganization],
    FILTER(
        'DateDim',
        'DateDim'[YearMonth] = @YearMonth
    )
)

     

  • hackcrr's avatar
    hackcrr
    2 years ago

    If the duplication is due to multiple rows in the dataset having the same PhysicianOrganization value, consider adding a DISTINCT clause directly in the DAX query or in the SQL query that gets the data.
    Ensure that the slicer displays only distinct values. You can modify the DAX query to return distinct PhysicianOrganization values. For example, you can wrap your SUMMARIZECOLUMNS function with the following DISTINCT function:

    EVALUATE 
DISTINCT(
    SUMMARIZECOLUMNS(
        'DateDim'[YearMonth],
        'Providers'[PhysicianOrganization]
    )
)

     

     

    hackcrr

    If I have answered your question, please mark my reply as solution and kudos to this post, thank you!

2 Replies

  • aduguid's avatar
    aduguid
    Memorable Member
    2 years ago

    Try this as your dataset2

     

    EVALUATE 
SUMMARIZECOLUMNS(
    'Providers'[PhysicianOrganization],
    FILTER(
        'DateDim',
        'DateDim'[YearMonth] = @YearMonth
    )
)

     

  • hackcrr's avatar
    hackcrr
    Memorable Member
    2 years ago

    If the duplication is due to multiple rows in the dataset having the same PhysicianOrganization value, consider adding a DISTINCT clause directly in the DAX query or in the SQL query that gets the data.
    Ensure that the slicer displays only distinct values. You can modify the DAX query to return distinct PhysicianOrganization values. For example, you can wrap your SUMMARIZECOLUMNS function with the following DISTINCT function:

    EVALUATE 
DISTINCT(
    SUMMARIZECOLUMNS(
        'DateDim'[YearMonth],
        'Providers'[PhysicianOrganization]
    )
)

     

     

    hackcrr

    If I have answered your question, please mark my reply as solution and kudos to this post, thank you!