none
Passing Paramaters in DAX native query via Power Query (M) RRS feed

  • Question

  • Dear all,

    i have been searching forums and Bblogs for the last 2 days but i couldnt come up with a solution. 

    Our organization is using SSAS, cubes are developed for specific purposes and i need to get some part of the cube for my own analysis.

    To do that i use dax native query in import mode. Now i want to implement some paramaters. Via the dax studio, when i test below query paramater works, but when i put in Power Bi desktop and even if i create a Paramater in Power Query with the same name, i get the below error:

    "DataSource.Error: AnalysisServices: The query contains the CountryCode parameter, which is not declared."

     

    I tried to alter the M code many times and i used Value.NativeQuery() syntax as well, none of them seems working

    Below is my dax query

     

    EVALUATE
     (
        ADDCOLUMNS (
            SUMMARIZE (
                FILTER (
                    VALUES ( Facts ),
                    RELATED ( 'Date'[Year] )
                        >= YEAR ( NOW () ) - 1
                        && RELATED ( Customer[Customer Segmentation Level 1 Code] ) = "WSALE"
                        && RELATED ( 'Initiating Office'[Initiating Office MIS Country Code] ) = @CountryCode
                ),
         
            XXX
            XXX
             XXX
             XXX
             'Customer'[Customer Segmentation Level 1 Code],      
             'Initiating Office'[Initiating Office MIS Country Code],
             'Date'[Year]
            ),
            "Total", CALCULATE ( SUM ( 'Facts'[ Income] ) ),
            
        )
    )

    The aim is to implement a paramater list of country codes connected to an excel file so end users can just change the excel file and after a refresh power query sends the dax to the data base and pulls out the new data based on the country code. Not sure if my way of thinking is correct which is why finally i am posting it here, after searching for a while

     

    Thank you in advance

     

     

    Wednesday, October 23, 2019 10:30 AM

Answers

  • Hi Lepeltieri,

    you have to reference the parameter in a different way. Unfortunately you cannot to this in the UI-window where you pasted the DAX-code originally, but have to edit the generated M-code manually:

    Its a bit of a pain, as the formatting of the auto-generated code is terrible.

    To get it started, replace @CountryCode by a dummy value like this "10".

    Then edit the code in the advanced editor. There the "10" will show up as ""10""

    Replace this value by  """ & CountryCode & """

    & CountryCode & should then appear in black (indicating this is M-code)  and everything else should be read (indicating thats text)


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by Lepeltieri Tuesday, October 29, 2019 3:03 PM
    Saturday, October 26, 2019 8:37 AM
    Moderator

All replies

  • Hi Lepeltieri,

    you have to reference the parameter in a different way. Unfortunately you cannot to this in the UI-window where you pasted the DAX-code originally, but have to edit the generated M-code manually:

    Its a bit of a pain, as the formatting of the auto-generated code is terrible.

    To get it started, replace @CountryCode by a dummy value like this "10".

    Then edit the code in the advanced editor. There the "10" will show up as ""10""

    Replace this value by  """ & CountryCode & """

    & CountryCode & should then appear in black (indicating this is M-code)  and everything else should be read (indicating thats text)


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by Lepeltieri Tuesday, October 29, 2019 3:03 PM
    Saturday, October 26, 2019 8:37 AM
    Moderator
  • Thank you so much Imke, worked like a charm!
    Tuesday, October 29, 2019 3:03 PM