DAX: Use renamed columns in visual

[Heinrich]

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.

Regards

Heinrich

[Heinrich]

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]

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]

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]

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]

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

[Heinrich]

Hello @v-kpoloju-msft 
I managed to run it:

// DAX Query
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"

When I receive a mail from Flow the words are in Brackets. Is it possible to remove them.
Regards
Heinrich

[v-kpoloju-msft · Accepted solution]

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]

Hi @v-kpoloju-msft 
Thank you for your help
The difference between your script and mine was 

your script: Order by / SBC

mine: Order by / "SBC"

Your proposal throwed an error
"Query (24, 2) Failed to resolve name 'SBC'."

with the char " it works.

I have done exactly that but the brackets keep showing up.

I have done it within Power Automate by editing the HTML Table.

Do you have an idea 

Have a great day

Heinrich

[pankajnamekar25]

Hello @Heinrich 

Yes, you can use the renamed column names (the ones you see in the Fields pane) in DAX even in DirectQuery mode and when using it with Power Automate  but with a key condition:

If you're writing your DAX inside Power BI, you must use the original table and column names (as they appear in the Data Model, not what you've renamed in visuals).

But when you're using Power Automate for Power BI button flows, the DAX used in Power Automate still references the model column names, not the display names used in visuals.

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.