Forum Discussion

Heinrich's avatar
Heinrich
Icon for Post Partisan rankPost 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.

 

Regards

Heinrich

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-kpoloju-msft's avatar
    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.

9 Replies

  • pankajnamekar25's avatar
    pankajnamekar25
    Icon for Super User rankSuper User
    1 year ago

    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.

  • Heinrich's avatar
    Heinrich
    Icon for Post Partisan rankPost 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's avatar
      v-kpoloju-msft
      Icon for Community Support rankCommunity 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's avatar
        Heinrich
        Icon for Post Partisan rankPost 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