Forum Discussion

spaceman127's avatar
spaceman127
Super User
2 years ago
Solved

Problem with a DAX Query in DAX Studio

Hello Community,

I have a problem with the following DAX query. Later I would like to use it in Power Bi Report Builder, but first I need to have a working query.

 

In principle, if ALL is selected, it should perform the first calculation, if not then the second.

 

 

I always get the message :
8/21/2024 2:57:35 PM Query (34, 9) The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

 

Unfortunately I can't find the error or I don't quite understand why.

Where is my error and how can I correct it?

This is my DAX Query.

 

 

DEFINE

VAR __DS0FilterJahrTable = 
    TREATAS({"2024"}, 'Datumstabelle'[Jahr])

VAR __DS0FilterMonatTable2 = 
    TREATAS({"April"}, 'Datumstabelle'[Monat])

VAR __DS0FilterSubscriptionsTable3 = 
		TREATAS(
			{"Test Subscription"},
			'dimAzSubscriptions'[subscriptionName])

VAR __DS0FilterTenantTable4 = 
		TREATAS({"Testtenant"}, 'dimAzTenants'[TenantName])

VAR __DS0FilterTable5 = 
		TREATAS({"ALL"}, 'dimAzTags'[Tagname])

VAR __DS0FilterTable6 = 
		TREATAS({"ALL"}, 'dimAzTags'[Valuename])

VAR __DS0Core = 
    IF (
         __DS0FilterTable5 = TRUE() && __DS0FilterTable6 = TRUE(),
        CALCULATETABLE(
            SUMMARIZECOLUMNS(
                'dimAzSubscriptions'[subscriptionName], 
                'dimAzResources'[Ressourcenname],
                "Azure Kosten", 'Measuretable'[Azure Kosten]
            ), 
            __DS0FilterJahrTable, __DS0FilterMonatTable2,  __DS0FilterSubscriptionsTable3, __DS0FilterTenantTable4
        ),
        CALCULATETABLE(
            SUMMARIZECOLUMNS(
                'dimAzSubscriptions'[subscriptionName], 
                'dimAzResources'[Ressourcenname],
                "Azure Kosten", 'Measuretable'[Azure Kosten]
            ), 
            __DS0FilterJahrTable, __DS0FilterMonatTable2,  __DS0FilterSubscriptionsTable3, __DS0FilterTenantTable4, __DS0FilterTable5, __DS0FilterTable6
        )
    )

EVALUATE __DS0Core

 

 

Many thanks in advance.

Best regards

  • spaceman127 - Are you attempting to create a calculated table?

     

    If so this will not work, because they cannot be created based on the input to filtering. Calculated tables are created at the point the model is refreshed, so do not accept inputs. 

     

    You are getting this error in DAX Studio because it is for testing the results of scalar values, and this query looks to be for a table. 

  • spaceman127 - I do not have any experience with Paginated Reports, so I'll leave you in the hands of Ankur04 .

     

    As I have answered your orginal query, which was why the DAX would not work (and you are on a DAX part to the forum), I would be greatful if you can accept my answer as the solution (it helps for visibility for others). 

     

    There can be mulitple solutions to a query, so when Ankur04 helps you with the query you need, that can be accepted also. 

5 Replies

  • Hi ,

     

    If I understand correctly, you want to develope a paginated report and you are trying to generate the query in powerbi first then you will use that query in you paginated report dataset and the report will have parameter and based on that selection you want to switch the calculation.

     

    I would recommend you to create a query in paginated dataset design mode and then customize it as per need. that would be simple.

     

    would love help further if you face any difficulty.

     

    Thanks,

     

    • spaceman127's avatar
      spaceman127
      Super User

      Ankur04  thank you for anwser.

       

      Yes, I would like to create a Pagnited Report.

      Can you give me an example of how something like this could look with the Query Designer? I haven't worked with it much yet.

  • spaceman127 - Are you attempting to create a calculated table?

     

    If so this will not work, because they cannot be created based on the input to filtering. Calculated tables are created at the point the model is refreshed, so do not accept inputs. 

     

    You are getting this error in DAX Studio because it is for testing the results of scalar values, and this query looks to be for a table. 

    • spaceman127's avatar
      spaceman127
      Super User

      mark_endicott  - Thank you for your anwser.

       

      I have understood that it does not work.

       

      What can I use instead to replace the calculated table?

      I need this condition to check whether the variables __DS0FilterTable5 and __DS0FilterTable6 are filled with the value “ALL”.
      Accordingly, it must then perform one or the other calculation.

       

      Many greetz

      • mark_endicott's avatar
        mark_endicott
        Super User

        spaceman127 - I do not have any experience with Paginated Reports, so I'll leave you in the hands of Ankur04 .

         

        As I have answered your orginal query, which was why the DAX would not work (and you are on a DAX part to the forum), I would be greatful if you can accept my answer as the solution (it helps for visibility for others). 

         

        There can be mulitple solutions to a query, so when Ankur04 helps you with the query you need, that can be accepted also.