Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JWick1969
Helper IV
Helper IV

Power Bi Report Builder - DAX Query from Performance analyzer multiple parameter values issue

Hi All,

 

I have created a report using PowerBI Report builder and the dataset query is coming from Performance Analyzer.

I define a Year and Month parameter but if I choose a multiple value on year or month i'm getting an error or no data displayed on the report. If i choose single value on the parameter the report is working. 

 

Another thing, if i choose multiple value on year parameter, i'm also getting an error.

cannot covert value <pii>2019|2020</pii> of type tp type integer while processing column <pii>[static column]->datetable[Received Year]</pii>

 

Parameter: set expression value

=Join(Parameters!Year.Value,"|")

=Join(Parameters!Month.Value,"|")

 

 

Below is the DAX query.

 

// DAX Query
DEFINE


VAR __DS0FilterTable =
TREATAS({@Year}, 'DateTable'[Received Year])


VAR __DS0FilterTable1 =
TREATAS({@Month}, 'DateTable'[MonthName])


VAR __DS0FilterTable2 =
TREATAS({"Phone"}, 'CSST'[Product Group])

 

VAR __DS0FilterTable3 =
TREATAS({"AER"}, 'CCST'[Category])

 


EVALUATE
TOPN(
501,
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
'CSST'[Category], "IsGrandTotalRowTotal",
'CSST'[Client], "IsDM0Total",

 

I believe the parameters are just not working correctly or the DAX query, likely, something wrong I did, but I have no idea how to fix this. I have tried innumerous things! Please help! 

 

Thank you!

2 ACCEPTED SOLUTIONS

So the basic order of operations for building a query as an expression is as follows:

 

1) you create a new data set and link it to a connection.

2) then you click the fx button to open the expression editor

3) then you start the expression with an equals and an opening double quote character eg.  =" 
4) then I normally open a text editor paste my query in there and do a global search for any double quote characters " and replace them with 2 double quote characters ""

5) then you paste in your query and at the end you add a closing double quote character "

 

 

="DEFINE
  VAR __DS0FilterTable = 
    TREATAS({2021}, 'DateTable'[Received])

  VAR __DS0FilterTable2 = 
    TREATAS({""November""}, 'CCST'[MonthName])

  VAR __DS0FilterTable3 = 
    TREATAS({""LEGACY""}, 'CCST'[Model])

  VAR __DS0FilterTable4 = 
    TREATAS({""DIY""}, 'CCST'[Type])

  VAR __DS0FilterTable5 = 
    TREATAS({""Normal""}, 'CCST'[Status])

  VAR __DS0FilterTable6 = 
    TREATAS({""OTHERS""}, 'CCST'[Category])

EVALUATE
  TOPN(
    501,
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Year], ""IsGrandTotalRowTotal"",
        ROLLUPGROUP(
          'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Month],
          'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[MonthNo]
        ), ""IsDM0Total""
      ),
      __DS0FilterTable,
      __DS0FilterTable2,
      __DS0FilterTable3,
      __DS0FilterTable4,
      __DS0FilterTable5,
      __DS0FilterTable6,
      ""SumReceived"", CALCULATE(SUM('CCST'[Qty])),
      ""Good"", 'CCST'[Good],
      ""Good__"", 'CCST'[Good %]
    ),
    [IsGrandTotalRowTotal],
    1,
    'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Year],
    1,
    [IsDM0Total],
    1,
    'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[MonthNo],
    1,
    'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Month],
    1
  )

ORDER BY
  [IsGrandTotalRowTotal],
  'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Year],
  [IsDM0Total],
  'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[MonthNo],
  'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Month]
  "

 

 

So the above is just a hard coded expression, to incorporate parameters in you would take the exising hard coded filters and replace them. So the following month filter

 

    TREATAS({""November""}, 'CCST'[MonthName])

 

would become something like the following to manually join in the value from a parameter called Month

    TREATAS({"""  + Parameters!Month.Value + """}, 'CCST'[MonthName])

 

View solution in original post


@JWick1969 wrote:

I'm not experiencing an error. so far i encountered no data displayed on my report.  Imap the query field to field by copying the one from the query code.


This will be the cause of your problem. Because of the way Report Builder executes dax queries certain characters in the fields names get replaced with underscores. So if you have not mapped using the correctly encoded names you will just get an empty string in your fields. So if you have asked for the field "[Sales Amount]" but the query engine in Report Builder returns it as "__Sales_Amount__" then your dataset will contain rows, but all the fields will be blank.

 

It is much easier to use the approach I suggested in my previous reply and paste in the raw query first and let Report Builder generate the field mappings before you turn your query into an expression.

View solution in original post

25 REPLIES 25
JWick1969
Helper IV
Helper IV

Thank you for the reply. By the way, How can I  change the code for TREATAS to accomodate multiple values.


@JWick1969 wrote:

Thank you for the reply. By the way, How can I  change the code for TREATAS to accomodate multiple values.


Treatas will accomodate multiple values, the problem is mapping the parameter from Report Builder to the parameter in the query.

 

If we take the top 3 lines of your query as an example

// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({@Year}, 'DateTable'[Received Year])

 

Currently with the JOIN in your parameter expression this will expand to the following with a single string value:

// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({"2019|2020"}, 'DateTable'[Received Year])

 

What it needs to  be is something like the following (assuming your Year is an integer column)

// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({2019,2020}, 'DateTable'[Received Year])

 

The only way I know to use TREATAS is to click the fx button on the query and build the query as a dynamic expression using something like the following (option 2 from my first post)

 

You will noticed the red doubling up of quotes that is required in order to embed a quote inside a quoted string expression. This is the tricky bit of building your query this way. If you don't escape all the quotes correctly the report will not run and it can be tricky to find mistakes. But once you have it working it functions well. I have one report I build like this which is has about 15 optional parameters so I actually include/exclude entire sections of the query based on whether the parameters have a value or not.

 

="

// DAX Query
DEFINE


VAR __DS0FilterTable =
TREATAS({" + Join(Parameters!Year.Value,",") + "}, 'DateTable'[Received Year])


VAR __DS0FilterTable1 =
TREATAS({""" + Join(Parameters!Month.Value,""",""") + """}, 'DateTable'[MonthName])


VAR __DS0FilterTable2 =
TREATAS({""Phone""}, 'CSST'[Product Group])

 

VAR __DS0FilterTable3 =
TREATAS({""AER""}, 'CCST'[Category])


EVALUATE
TOPN(
501,
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
'CSST'[Category], ""IsGrandTotalRowTotal"",
'CSST'[Client], ""IsDM0Total"","

HI, 

 

I'm having an error  could not figure out on where to put the quotes  which I used your sample. Anyway, I'm insert new DAX Query and optimize some of the column to shorten the code.

DEFINE
  VAR __DS0FilterTable = 
    TREATAS({2021}, 'DateTable'[Received])

  VAR __DS0FilterTable2 = 
    TREATAS({"November"}, 'CCST'[MonthName])

  VAR __DS0FilterTable3 = 
    TREATAS({"LEGACY"}, 'CCST'[Model])

  VAR __DS0FilterTable4 = 
    TREATAS({"DIY"}, 'CCST'[Type])

  VAR __DS0FilterTable5 = 
    TREATAS({"Normal"}, 'CCST'[Status])

  VAR __DS0FilterTable6 = 
    TREATAS({"OTHERS"}, 'CCST'[Category])

EVALUATE
  TOPN(
    501,
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Year], "IsGrandTotalRowTotal",
        ROLLUPGROUP(
          'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Month],
          'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[MonthNo]
        ), "IsDM0Total"
      ),
      __DS0FilterTable,
      __DS0FilterTable2,
      __DS0FilterTable3,
      __DS0FilterTable4,
      __DS0FilterTable5,
      __DS0FilterTable6,
      "SumReceived", CALCULATE(SUM('CCST'[Qty])),
      "Good", 'CCST'[Good],
      "Good__", 'CCST'[Good %]
    ),
    [IsGrandTotalRowTotal],
    1,
    'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Year],
    1,
    [IsDM0Total],
    1,
    'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[MonthNo],
    1,
    'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Month],
    1
  )

ORDER BY
  [IsGrandTotalRowTotal],
  'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Year],
  [IsDM0Total],
  'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[MonthNo],
  'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Month]



So the basic order of operations for building a query as an expression is as follows:

 

1) you create a new data set and link it to a connection.

2) then you click the fx button to open the expression editor

3) then you start the expression with an equals and an opening double quote character eg.  =" 
4) then I normally open a text editor paste my query in there and do a global search for any double quote characters " and replace them with 2 double quote characters ""

5) then you paste in your query and at the end you add a closing double quote character "

 

 

="DEFINE
  VAR __DS0FilterTable = 
    TREATAS({2021}, 'DateTable'[Received])

  VAR __DS0FilterTable2 = 
    TREATAS({""November""}, 'CCST'[MonthName])

  VAR __DS0FilterTable3 = 
    TREATAS({""LEGACY""}, 'CCST'[Model])

  VAR __DS0FilterTable4 = 
    TREATAS({""DIY""}, 'CCST'[Type])

  VAR __DS0FilterTable5 = 
    TREATAS({""Normal""}, 'CCST'[Status])

  VAR __DS0FilterTable6 = 
    TREATAS({""OTHERS""}, 'CCST'[Category])

EVALUATE
  TOPN(
    501,
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Year], ""IsGrandTotalRowTotal"",
        ROLLUPGROUP(
          'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Month],
          'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[MonthNo]
        ), ""IsDM0Total""
      ),
      __DS0FilterTable,
      __DS0FilterTable2,
      __DS0FilterTable3,
      __DS0FilterTable4,
      __DS0FilterTable5,
      __DS0FilterTable6,
      ""SumReceived"", CALCULATE(SUM('CCST'[Qty])),
      ""Good"", 'CCST'[Good],
      ""Good__"", 'CCST'[Good %]
    ),
    [IsGrandTotalRowTotal],
    1,
    'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Year],
    1,
    [IsDM0Total],
    1,
    'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[MonthNo],
    1,
    'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Month],
    1
  )

ORDER BY
  [IsGrandTotalRowTotal],
  'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Year],
  [IsDM0Total],
  'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[MonthNo],
  'LocalDateTable_9a516b7e-ac4d-49d6-a22c-5318bf076cd8'[Month]
  "

 

 

So the above is just a hard coded expression, to incorporate parameters in you would take the exising hard coded filters and replace them. So the following month filter

 

    TREATAS({""November""}, 'CCST'[MonthName])

 

would become something like the following to manually join in the value from a parameter called Month

    TREATAS({"""  + Parameters!Month.Value + """}, 'CCST'[MonthName])

 

I have a similiar case but instead of using parameters for the filter I want to use parameters to dynamically bring a different field. The problem with that is that summrize columns does not give aliases to the columns So I wouldn't know what to name it in the Fields section. I tried Nesting SelectedColumns on top of Summarize but then I would get empty values for "Actuals" column

 

 

="DEFINE

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(

          'Dim_Acc'[GEN_MEMBER3_DESC],
          'Dim_Acc'[GEN_MEMBER4_DESC],
          'Dim_Acc'[GEN_MEMBER4_ID],
          'Dim_Acc'[GEN_MEMBER5_DESC],
          'Dim_Acc'[GEN_MEMBER5_ID],
          'Dim_CostCenter'[Segment ID Desc],
          ("""  + Parameters!Field.Value + """),
      ""Actuals MTD"", 'Financial_Measures'[Actuals MTD]
    )


EVALUATE
  __DS0Core

"

 

emartes_0-1643234974521.png

 


@emartes wrote:

I tried Nesting SelectedColumns on top of Summarize but then I would get empty values for "Actuals" column

 


This is the only approach I can think of for this scenario. What did your expression look like for the SELECTCOLUMNS function?

This is what I actually ran for testing purposes

DEFINE
  VAR __DS0FilterTable = 
    TREATAS({"No Calc"}, 'Analytic'[Analytic])

  VAR __DS0FilterTable2 = 
    TREATAS({"Periodic"}, 'ViewTyp'[View])

  VAR __DS0FilterTable3 = 
    TREATAS({"Dec-21"}, 'Dim_Date'[Month])

  VAR __DS0FilterTable4 = 
    TREATAS({"TSG"}, 'Dim_CostCenter'[SEGMENT_CD])

  VAR __DS0FilterTable5 = 
    TREATAS(
      {("TSG - Title Services Segment", "DTO - Domestic Title Operations")},
      'Dim_CostCenter'[Segment ID Desc],
      'Dim_CostCenter'[Subsegment ID Desc]
    )
	

  
  VAR __DS0Core = 
   SELECTCOLUMNS(
    SUMMARIZECOLUMNS(
      'Dim_Acc'[GEN_MEMBER3_DESC],
      'Dim_Acc'[GEN_MEMBER4_DESC],
      'Dim_Acc'[GEN_MEMBER4_ID],
      __DS0FilterTable,
      __DS0FilterTable2,
      __DS0FilterTable3,
      __DS0FilterTable4,
      __DS0FilterTable5,
      "Actuals_MTD", 'Financial_Measures'[Actuals MTD]
		),
		"Acc 4", 'Dim_Acc'[GEN_MEMBER4_DESC],
		"Actuals_MTD", [Actuals MTD]
    )

EVALUATE
  __DS0Core

and this is the result

 

emartes_0-1643236296773.png

Results removing the selectColumns part:

emartes_1-1643236400459.png

 

I'm thinking that maybe using summarize instead of summarizeColumns could be an option.

@d_gosbell I noticed I was using the wrong name for the Actuals, I was missing the under score. Not it works!

DEFINE
  VAR __DS0FilterTable = 
    TREATAS({"No Calc"}, 'Analytic'[Analytic])

  VAR __DS0FilterTable2 = 
    TREATAS({"Periodic"}, 'ViewTyp'[View])

  VAR __DS0FilterTable3 = 
    TREATAS({"Dec-21"}, 'Dim_Date'[Month])

  VAR __DS0FilterTable4 = 
    TREATAS({"TSG"}, 'Dim_CostCenter'[SEGMENT_CD])

  VAR __DS0FilterTable5 = 
    TREATAS(
      {("TSG - Title Services Segment", "DTO - Domestic Title Operations")},
      'Dim_CostCenter'[Segment ID Desc],
      'Dim_CostCenter'[Subsegment ID Desc]
    )
	

  
  VAR __DS0Core = 
	SELECTCOLUMNS(
    SUMMARIZECOLUMNS(
      'Dim_Acc'[GEN_MEMBER3_DESC],
      'Dim_Acc'[GEN_MEMBER4_DESC],
      'Dim_Acc'[GEN_MEMBER4_ID],
      __DS0FilterTable,
      __DS0FilterTable2,
      __DS0FilterTable3,
      __DS0FilterTable4,
      __DS0FilterTable5,
      "Actuals_MTD", 'Financial_Measures'[Actuals MTD]
		),
		"Acc 4", 'Dim_Acc'[GEN_MEMBER4_DESC],
		"Actuals_MTD", [Actuals_MTD]
)

EVALUATE
  __DS0Core

emartes_2-1643238190905.png

Now I'm facing anothe issue after putting in the parameter in the query

 

="DEFINE
  VAR __DS0FilterTable = 
    TREATAS({""No Calc""}, 'Analytic'[Analytic])

  VAR __DS0FilterTable2 = 
    TREATAS({""Periodic""}, 'ViewTyp'[View])

  VAR __DS0FilterTable3 = 
    TREATAS({""Dec-21""}, 'Dim_Date'[Month])

  VAR __DS0FilterTable4 = 
    TREATAS({""TSG""}, 'Dim_CostCenter'[SEGMENT_CD])

  VAR __DS0FilterTable5 = 
    TREATAS(
      {(""TSG - Title Services Segment"", ""DTO - Domestic Title Operations"")},
      'Dim_CostCenter'[Segment ID Desc],
      'Dim_CostCenter'[Subsegment ID Desc]
    )

  VAR __DS0Core = 
  SelectColumns(
    SUMMARIZECOLUMNS(

          'Dim_Acc'[GEN_MEMBER3_DESC],
          'Dim_Acc'[GEN_MEMBER4_DESC],
          'Dim_Acc'[GEN_MEMBER4_ID],
          'Dim_Acc'[GEN_MEMBER5_DESC],
          'Dim_Acc'[GEN_MEMBER5_ID],
          'Dim_Acc'[GEN_MEMBER6_DESC],
          'Dim_Acc'[GEN_MEMBER6_ID],
          'Dim_Acc'[GEN_MEMBER7_DESC],
          'Dim_Acc'[GEN_MEMBER7_ID],
          'Dim_Acc'[GEN_MEMBER8_DESC],
          'Dim_Acc'[GEN_MEMBER8_ID],
          'Dim_Acc'[GEN_MEMBER9_DESC],
          'Dim_Acc'[GEN_MEMBER9_ID],
          'Dim_Acc'[GEN_MEMBER10_DESC],
          'Dim_CostCenter'[Segment ID Desc],
          ("""  + Parameters!CC_Group.Value + """),
      __DS0FilterTable,
      __DS0FilterTable2,
      __DS0FilterTable3,
      __DS0FilterTable4,
      __DS0FilterTable5,
      ""Actuals_MTD"", 'Financial_Measures'[Actuals MTD]
    ),
    ""ACC_3"", 'Dim_Acc'[GEN_MEMBER3_DESC],
    ""ACC_4"", 'Dim_Acc'[GEN_MEMBER4_DESC],
    ""CFIELD"", ("""  + Parameters!CC_Group.Value + """),
    ""Actuals"", [Actuals_MTD]
  )

EVALUATE
  __DS0Core

"

 

But when I run the querry using 'Dim_CostCenter'[Subsegment ID Desc] as the paremeter value I get the following:

emartes_3-1643238341633.png

 

I tried running it without parenthesis around the parameter but still the same.

 

The way you are doing it at the moment will insert the column reference as a string in paranthesis so the value would come out as ("'Dim_CostCenter'[Subsegment ID Desc]") which is not valid. You probably just need to remove the paranthesis and 2 of the double quotes so that you just inject the column reference. 

 

eg.

 

="DEFINE
  VAR __DS0FilterTable = 
    TREATAS({""No Calc""}, 'Analytic'[Analytic])

  VAR __DS0FilterTable2 = 
    TREATAS({""Periodic""}, 'ViewTyp'[View])

  VAR __DS0FilterTable3 = 
    TREATAS({""Dec-21""}, 'Dim_Date'[Month])

  VAR __DS0FilterTable4 = 
    TREATAS({""TSG""}, 'Dim_CostCenter'[SEGMENT_CD])

  VAR __DS0FilterTable5 = 
    TREATAS(
      {(""TSG - Title Services Segment"", ""DTO - Domestic Title Operations"")},
      'Dim_CostCenter'[Segment ID Desc],
      'Dim_CostCenter'[Subsegment ID Desc]
    )

  VAR __DS0Core = 
  SelectColumns(
    SUMMARIZECOLUMNS(

          'Dim_Acc'[GEN_MEMBER3_DESC],
          'Dim_Acc'[GEN_MEMBER4_DESC],
          'Dim_Acc'[GEN_MEMBER4_ID],
          'Dim_Acc'[GEN_MEMBER5_DESC],
          'Dim_Acc'[GEN_MEMBER5_ID],
          'Dim_Acc'[GEN_MEMBER6_DESC],
          'Dim_Acc'[GEN_MEMBER6_ID],
          'Dim_Acc'[GEN_MEMBER7_DESC],
          'Dim_Acc'[GEN_MEMBER7_ID],
          'Dim_Acc'[GEN_MEMBER8_DESC],
          'Dim_Acc'[GEN_MEMBER8_ID],
          'Dim_Acc'[GEN_MEMBER9_DESC],
          'Dim_Acc'[GEN_MEMBER9_ID],
          'Dim_Acc'[GEN_MEMBER10_DESC],
          'Dim_CostCenter'[Segment ID Desc],
          "  + Parameters!CC_Group.Value + ",
      __DS0FilterTable,
      __DS0FilterTable2,
      __DS0FilterTable3,
      __DS0FilterTable4,
      __DS0FilterTable5,
      ""Actuals_MTD"", 'Financial_Measures'[Actuals MTD]
    ),
    ""ACC_3"", 'Dim_Acc'[GEN_MEMBER3_DESC],
    ""ACC_4"", 'Dim_Acc'[GEN_MEMBER4_DESC],
    ""CFIELD"", "  + Parameters!CC_Group.Value + ",
    ""Actuals"", [Actuals_MTD]
  )

EVALUATE
  __DS0Core

"

  

@d_gosbell yes it worked! thank you for your help. This could probably be another thread about dynamic fields using power bi data sets. But this thread was the closest to what I was looking for. Thanks again for the help!

I think the issue is that inside your SUMMARIZECOLUMNS you are aliasing the column as "Actuals_MTD" then you are referencing it with a space in the SELECTCOLUMNS. The last line of your SELECTCOLUMNS should look as follows since it is operating on the output from the SUMMARIZECOLUMNS which does not have a column called [Actuals MTD]

 

"Actuals_MTD", [Actuals_MTD]

 

This is what I did. I create a new dataset and click the properties then on the query type I paste the query and tried also to paste the query in expression(Fx). Click the fields properties then click the add select the query field and filled up the field source and fieldname which is coming from the query. then I run the the report and No data displayed.

 

JWick1969_1-1639042957735.png

 

 

JWick1969_2-1639043094523.png

 

It will be throwing an error somewhere since expressions in the fields in Report Builder use VBA (since they need to be data source agnostic) and what you have there is DAX. I tend avoid using calculated fields in Report Builder where ever possible and put things like that into the query so that they get executed by the tabular engine which will give you much better performance.

 

And I actually missed a step in my workflow. I actually paste the un-altered query in and refresh the fields first, then I click the fx and turn it into an expression. That maps the query fields to fields in report builder which you can then drag and drop onto your report

I'm not experiencing an error. so far i encountered no data displayed on my report.  Imap the query field to field by copying the one from the query code.


@JWick1969 wrote:

I'm not experiencing an error. so far i encountered no data displayed on my report.  Imap the query field to field by copying the one from the query code.


This will be the cause of your problem. Because of the way Report Builder executes dax queries certain characters in the fields names get replaced with underscores. So if you have not mapped using the correctly encoded names you will just get an empty string in your fields. So if you have asked for the field "[Sales Amount]" but the query engine in Report Builder returns it as "__Sales_Amount__" then your dataset will contain rows, but all the fields will be blank.

 

It is much easier to use the approach I suggested in my previous reply and paste in the raw query first and let Report Builder generate the field mappings before you turn your query into an expression.

The report is already working if i choose one year data. but i'm having issues if i pulled data three years data. I have 33 columns on my report.  May I know what are the fixes should I do in regards with this error. Thanks. by the way, below is the error. 

 

Resource Governing: This query uses more memory than the configured limit.
The query — or calculations referenced by it — might be too memory-intensive to run.
To run this query, you could simplify the query or its calculations, or reach out to your capacity administrator to see if they can increase the per-query memory limit.

 

 

So the two possible fixes for this are spelled out in the error message itself.


@JWick1969 wrote:

To run this query, you could simplify the query or its calculations, or reach out to your capacity administrator to see if they can increase the per-query memory limit.


As long as you are not trying to export millions of records the issue is most likely one of your measures requiring a lot of memory to calculate. 

 

I would suggest copying your query out to something like DAX Studio or SSMS where you can easily modify it an run it repeatedly. Then comment out all your measures and include a simple measure like one that is just a SUM of a single column or a COUNTROWS. If the query works with a simple measure then gradually add your other measures back in one at a time until you find which one(s) cause the issue. The you will need to see if you can re-write / optimize those.

Thank you for your reply. by the way, I notice one of my parameter. I'm selecting all the values meaning i have 30 values and it show all the 30 items in my report parameter  if i choose (select All), How can i display only the "All" on my Report.  curretnly i did like this in my parameter expression =Join(Parameters!Year.Value,",").

'


@JWick1969 wrote:

Thank you for your reply. by the way, I notice one of my parameter. I'm selecting all the values meaning i have 30 values and it show all the 30 items in my report parameter  if i choose (select All), How can i display only the "All" on my Report.  curretnly i did like this in my parameter expression =Join(Parameters!Year.Value,",").

'


You could check in your expression if the number of values selected in the parameter matches the number of rows in the dataset you use to populate the parameter. So the expression would look something like the following, you would just need to change the field name and dataset names in red to match those in your report.

 

=iif( Parameters!Year.Value.Length = Count(Fields!Year.Value, "YearDataSet"), "ALL", Join(Parameters!Year.Value, ","))

Thank you for sharing this technique and I learned new knowledge on PowerBI report builder. I followed your approach and there's already data displayed on the report.🙂👍

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.