Report Builder - Issue with line chart average

[Danny2020]

Hello,

I have a database with quantities per part number, for multiple dates.

Usign power query I created a second table grouping the part numbers, so they are listed with no duplicates. I created a relationship between the 2 tables using part number, and published the dataset.

In report builder, I called as parameter the part number from the second table. The average quantity in the line chart remains the same, for any part number selected. How can I fix that issue?

Thank you

[Danny2020]

@v-nmadadi-msft , thank you so much for your solution. Changing to "Part_Number" from the database worked. However I'm still getting error message that Part_Number is not declared, but it lets me run the charts this time. Report Builder has a lot of issues, decades of old interface. It should be integrated within Power BI desktop.

[Danny2020]

Thank you @GrowthNatives . I modified the main Database query to include other columns. The idea is to have 4 line charts, each with the actual of the test, and the spec given from a third table "Specs". I could get an average of the actual in a calculated series in each chart. The parameter Part_Number should filter each chart.

EVALUATE SUMMARIZECOLUMNS('Database'[Date], 'Database'[Part Number], 'Specs'[Test1Spec)], 'Specs'[Test2Spec], 'Specs'[Test3Spec)],'Specs'[Test4Spec)],'Database'[Test1], 'Database'[Test2],'Database'[Test3],'Database'[Test4],

FILTER('Database', 'Database'[Part Number]=@Part_Number))

Validating the query, it has an error: "The query contains the 'Part_Number' parameter, which is not declared".

In the Dataset properties menu, I added the parameter PartNumber as a name, and in parameter value @Part_Number, from the second query:

EVALUATE SUMMARIZECOLUMNS('Database'[Part Number])

Any idea of what is causing the error?

[v-nmadadi-msft · Accepted solution]

Hi  @Danny2020 ,
Thanks for reaching out to the Microsoft fabric community forum.

Firstly to fix the error, In Report Builder and make sure the Name matches the DAX variable name exactly  for example, use @Part_Number instead of PartNumber. Then, in the Parameter Value field, link it to your report parameter by setting it to =Parameters!PartNumber.Value. This ensures that the DAX query recognizes and correctly applies the parameter value passed from the report.

Additionally,  make sure the parameter data type matches the column type in your model. If 'Database'[Part Number] is a text field, the report parameter should also be defined as text, if it’s numeric, then the parameter type must be numeric as well. Mismatched data types can prevent the filter from applying correctly and cause the query to fail.

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[GrowthNatives]

Hi @Danny2020 , 
For this you need to use the parameter to filter the dataset explicitly
If your dataset looks like this :

DAX 
EVALUATE
SUMMARIZECOLUMNS(
    'Table1'[Date],
    "Average Quantity", AVERAGE('Table1'[Quantity])
)

Change the Dax query to this 

DAX 
EVALUATE
SUMMARIZECOLUMNS(
    'Table1'[Date],
    FILTER(
        'Table1',
        'Table1'[PartNumber] = @PartNumber
    ),
    "Average Quantity", AVERAGE('Table1'[Quantity])
)

• @PartNumber is the Report Builder parameter name.
• Make sure your parameter data type matches (e.g., text vs numeric).
  
  ⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
  💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
  🚀Let’s keep building smarter, data-driven solutions together!🚀  [Explore More]