Forum Discussion

Post Partisan

1 year ago

Solved

DAX: Use renamed columns in visual

Hello I would like to use the renamed names in the DAX. The data used for the visual is in Direct Query and has another name. Is it possible? This DAX is used for Power Automate. Regard...

v-kpoloju-msft
1 year ago
Hi Heinrich,

Thank you for your detailed explanation and for sharing the DAX queries. I understand the two issues you're encountering, and I will address them both.

The error you're seeing suggests that the column 'PSTN Trunk FQDN' is being used in a calculation without an aggregation function. To fix this, you can apply an aggregation method such as SUM, MIN, MAX, or COUNT to the 'PSTN Trunk FQDN' column. Here's an updated example:

DAX:

"SumPSTN_NER_Good_Percentage", CALCULATE(SUM('CQD'[PSTN NER Good Percentage]), ALLEXCEPT('CQD', 'CQD'[PSTN Trunk FQDN]))

Concerning the issue with receiving emails from Flow where the column names appear in brackets, this is likely due to how the columns are referenced in your DAX query. Please ensure that the field names in the SELECTCOLUMNS function do not include any special characters such as brackets. The following updated query should resolve the issue:

DAX:

DEFINE VAR __DS0Core = SUMMARIZECOLUMNS( 'CQD'[PSTN Trunk FQDN], "SumPSTN_NER_Good_Percentage", CALCULATE(SUM('CQD'[PSTN NER Good Percentage])) ) VAR __DS0PrimaryWindowed = TOPN(501, __DS0Core, 'CQD'[PSTN Trunk FQDN], 1) EVALUATE SELECTCOLUMNS( __DS0PrimaryWindowed, // Use the filtered result set "SBC", 'CQD'[PSTN Trunk FQDN], // Rename 'PSTN Trunk FQDN' to SBC "NER", [SumPSTN_NER_Good_Percentage] // Rename 'SumPSTN_NER_Good_Percentage' to NER ) ORDER BY SBC

Please try these adjustments and let me know how it works for you. If you need further assistance, feel free to reach out!

Thank you.

Heinrich

Post Partisan

1 year ago

Hello pankajnamekar25
Hope you are doing well

This is a code part in Power BI
// DAX Query
DEFINE
    VAR __DS0FilterTable =
        FILTER(
            KEEPFILTERS(VALUES('123'[ABC])),
            NOT('123'[ABC] IN {"(Blank)"})
        )

The Values '123'[ABC] should be named DEF (Name with the Fields Pane of the Visual).

So according to you I have to use in Power BI = '123'[ABC]

But can use in Power Automate = DEF

Is that so?

Regards
Heinrich

v-kpoloju-msft

Community Support

1 year ago

Hi Heinrich,

Thank you for reaching out to the Microsoft Fabric Community Forum. Thank you pankajnamekar25, for your inputs on this thread.

Thanks for the clarification. You are correct inside DAX (Power BI) you must continue to use '123'[ABC]. However, to show/send DEF to Power Automate, you can manually rename (alias) the field using SELECTCOLUMNS, like this:

You Can use this Dax Measure in your original Dax:

SELECTCOLUMNS(

    '123',

    "DEF", [ABC]

)

This way, DAX uses the real field internally, but the output to Power Automate will have the label DEF.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

Heinrich

Post Partisan

1 year ago

Hello v-kpoloju-msft

Thank you for your answer.

I have this DAX.

Where to put this selectcolumns

'CQD'[PSTN Trunk FQDN] should be renamed in SBC

[SumPSTN_NER_Good_Percentage] should be renamed in NER

// DAX Query
DEFINE
	VAR __DS0FilterTable = 
		FILTER(
			KEEPFILTERS(VALUES('CQD'[PSTN Trunk FQDN])),
			NOT(	'CQD'[PSTN Trunk FQDN] IN {"(Blank)"})
		)

	VAR __DS0FilterTable2 = 
		TREATAS({"Conf",
			"P2P"}, 'CQD'[Session Type])

	VAR __DS0FilterTable3 = 
		TREATAS({"NonTest"}, 'CQD'[Test Call Type])

	VAR __DS0Core = 
		FILTER(
			KEEPFILTERS(
				SUMMARIZECOLUMNS(
					'CQD'[PSTN Trunk FQDN],
					__DS0FilterTable,
					__DS0FilterTable2,
					__DS0FilterTable3,
					"SumPSTN_NER_Good_Percentage", CALCULATE(SUM('CQD'[PSTN NER Good Percentage]))
				)
			),
			[SumPSTN_NER_Good_Percentage] < 10
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(501, __DS0Core, 'CQD'[PSTN Trunk FQDN], 1)

SELECTCOLUMNS(
   'CQD'[PSTN Trunk FQDN],
   SBC
)

EVALUATE
	__DS0PrimaryWindowed

ORDER BY
	'CQD'[PSTN Trunk FQDN]

Thank you very much

Heinrich

v-kpoloju-msft

Community Support

1 year ago

Hi Heinrich,

Thank you for the explanation and for sharing the DAX query. I understand that you want to rename the fields 'CQD'[PSTN Trunk FQDN] to SBC and [SumPSTN_NER_Good_Percentage] to NER in your query for integration with Power Automate.

To accomplish this, you can use the SELECTCOLUMNS function within your query to rename the fields accordingly. Here is how you can adjust your DAX to rename the columns before sending the results to Power Automate:

Revised Dax:

DEFINE

    VAR __DS0FilterTable =

        FILTER(

            KEEPFILTERS(VALUES('CQD'[PSTN Trunk FQDN])),

            NOT( 'CQD'[PSTN Trunk FQDN] IN {"(Blank)"} )

        )



    VAR __DS0FilterTable2 =

        TREATAS({"Conf", "P2P"}, 'CQD'[Session Type])



    VAR __DS0FilterTable3 =

        TREATAS({"NonTest"}, 'CQD'[Test Call Type])



    VAR __DS0Core =

        FILTER(

            KEEPFILTERS(

                SUMMARIZECOLUMNS(

                    'CQD'[PSTN Trunk FQDN],

                    __DS0FilterTable,

                    __DS0FilterTable2,

                    __DS0FilterTable3,

                    "SumPSTN_NER_Good_Percentage", CALCULATE(SUM('CQD'[PSTN NER Good Percentage]))

                )

            ),

            [SumPSTN_NER_Good_Percentage] < 10

        )



    VAR __DS0PrimaryWindowed =

        TOPN(501, __DS0Core, 'CQD'[PSTN Trunk FQDN], 1)



EVALUATE

    SELECTCOLUMNS(

        __DS0PrimaryWindowed,   // Use the filtered result set

        "SBC", 'CQD'[PSTN Trunk FQDN],  // Rename 'PSTN Trunk FQDN' to SBC

        "NER", [SumPSTN_NER_Good_Percentage]  // Rename 'SumPSTN_NER_Good_Percentage' to NER

    )

ORDER BY

    SBC

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

Heinrich

Post Partisan

1 year ago

Hello v-kpoloju-msft
I received this error:

{"error":{"code":"DatasetExecuteQueriesError","pbi.error":{"code":"DatasetExecuteQueriesError","parameters":{},"details":[{"code":"DetailsMessage","detail":{"type":1,"value":"Query (75, 5) A single value for column '<oii>PSTN Trunk FQDN</oii>' in table '<oii>CQD</oii>' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."}},{"code":"AnalysisServicesErrorCode","detail":{"type":1,"value":"3241803789"}}]}}}

This code was working well but with not edited names

// DAX Query
DEFINE
	VAR __DS0FilterTable = 
		FILTER(
			KEEPFILTERS(VALUES('CQD'[PSTN Trunk FQDN])),
			NOT('CQD'[PSTN Trunk FQDN] IN {"(Blank)"})
		)

	VAR __DS0FilterTable2 = 
		TREATAS({"Conf",
			"P2P"}, 'CQD'[Session Type])

	VAR __DS0FilterTable3 = 
		TREATAS({"NonTest"}, 'CQD'[Test Call Type])

	VAR __DS0Core = 
		FILTER(
			KEEPFILTERS(
				SUMMARIZECOLUMNS(
					'CQD'[PSTN Trunk FQDN],
					__DS0FilterTable,
					__DS0FilterTable2,
					__DS0FilterTable3,
					"SumPSTN_NER_Good_Percentage", CALCULATE(SUM('CQD'[PSTN NER Good Percentage]))
				)
			),
			[SumPSTN_NER_Good_Percentage] < 10
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(501, __DS0Core, 'CQD'[PSTN Trunk FQDN], 1)

EVALUATE
	__DS0PrimaryWindowed

ORDER BY
	'CQD'[PSTN Trunk FQDN]

Regards
Heinrich

Forum Discussion

DAX: Use renamed columns in visual

Related content

Renaming columns and adding columns

Conditionally rename columns

Views - Renaming Columns

Visual error when columns renamed

Rename columns from list