Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
JWick1969
Helper IV
Helper IV

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

‎11-29-2021 01:16 AM

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!

Labels:
Message 1 of 26
11,246 Views
0
2 ACCEPTED SOLUTIONS
d_gosbell
Super User
Super User
In response to JWick1969

‎12-08-2021 06:42 PM

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

Message 9 of 26
10,856 Views
d_gosbell
Super User
Super User
In response to JWick1969

‎12-09-2021 08:24 PM
@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

Message 14 of 26
10,789 Views
0
25 REPLIES 25
JWick1969
Helper IV
Helper IV

‎11-29-2021 05:07 AM

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

Message 3 of 26
10,317 Views
0
d_gosbell
Super User
Super User
In response to JWick1969

‎11-29-2021 01:30 PM
@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"","

Message 4 of 26
10,214 Views
JWick1969
Helper IV
Helper IV
In response to d_gosbell

‎12-08-2021 01:30 AM

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]



Message 8 of 26
10,036 Views
0
d_gosbell
Super User
Super User
In response to JWick1969

‎12-08-2021 06:42 PM

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])

 

Message 9 of 26
10,857 Views
Anonymous
Not applicable
In response to d_gosbell

‎01-26-2022 02:10 PM

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

 

Message 20 of 26
6,291 Views
0
d_gosbell
Super User
Super User
In response to Anonymous

‎01-26-2022 02:21 PM
@Anonymous 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?

Message 21 of 26
6,287 Views
0
Anonymous
Not applicable
In response to d_gosbell

‎01-26-2022 02:34 PM

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.

Message 22 of 26
6,267 Views
0
Anonymous
Not applicable
In response to Anonymous

‎01-26-2022 03:06 PM

@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.

 

Message 24 of 26
6,263 Views
0
d_gosbell
Super User
Super User
In response to Anonymous

‎01-26-2022 03:27 PM

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

"

  

Message 25 of 26
6,256 Views
Anonymous
Not applicable
In response to d_gosbell

‎01-27-2022 04:26 AM

@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!

Message 26 of 26
6,194 Views
0
d_gosbell
Super User
Super User
In response to Anonymous

‎01-26-2022 03:04 PM

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]

 

Message 23 of 26
6,263 Views
0
JWick1969
Helper IV
Helper IV
In response to d_gosbell

‎12-09-2021 04:49 PM

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

 

Message 11 of 26
9,961 Views
0
d_gosbell
Super User
Super User
In response to JWick1969

‎12-09-2021 05:54 PM

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

Message 12 of 26
9,953 Views
0
JWick1969
Helper IV
Helper IV
In response to d_gosbell

‎12-09-2021 08:07 PM

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.

Message 13 of 26
9,949 Views
0
d_gosbell
Super User
Super User
In response to JWick1969

‎12-09-2021 08:24 PM
@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.

Message 14 of 26
10,790 Views
0
JWick1969
Helper IV
Helper IV
In response to d_gosbell

‎12-13-2021 05:44 PM

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.

 

 

Message 16 of 26
9,728 Views
0
d_gosbell
Super User
Super User
In response to JWick1969

‎12-13-2021 07:56 PM

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.

Message 17 of 26
9,720 Views
0
JWick1969
Helper IV
Helper IV
In response to d_gosbell

‎12-14-2021 01:52 AM

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,",").

'

Message 18 of 26
9,713 Views
0
d_gosbell
Super User
Super User
In response to JWick1969

‎12-14-2021 01:58 PM
@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, ","))

Message 19 of 26
9,694 Views
0
JWick1969
Helper IV
Helper IV
In response to d_gosbell

‎12-10-2021 12:59 AM

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.🙂👍

Message 15 of 26
9,899 Views

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All