Problem with a DAX Query in DAX Studio

[spaceman127]

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

[mark_endicott · Accepted solution]

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

[Ankur04]

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]

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

[mark_endicott · Accepted solution]

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

@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 · Accepted solution]

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