Forum Discussion

Petanek333's avatar
Petanek333
Icon for Helper III rankHelper III
3 years ago

Dynamic value in a query used in Power Automate

Hi,

I have a report of account receivables and in the end I want to send 4 different tables via email to different sales representatives. 

Understandably I want to send each sales representative a table of only their customers. 

I found a great video how to do this in Power Automate: https://www.youtube.com/watch?v=tbNvNk11sXM 

Jason uses "Run a query against a dataset" option and copies the query of specific visual from Performance analyzer. 

My query would like this (I deleted all parts of the code that are not important):

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    TREATAS({"Sales representative A"}, 'Reps table'[Rep])

  VAR __DS0FilterTable2 = 
    FILTER(
      KEEPFILTERS(VALUES('My measure'[Measure])),
      ''My measure'[Measure] = 10
    )
  VAR __ValueFilterDM1 = 
    FILTER(
"these are not important parameters"
    )

The only important part is the first TREATAS function, where Sales representative A is selected. Suppose I have 5 Sales representatives, I could create 5 static power automate buttons and each of them would contain a query for the specific sales representative. But is there any way how to make one dynamic flow in power automate that would change the query parameters based on a slicer value selected? I don't know if I can use a variable in a power automate query.

3 Replies

  • You can pass dynamic content into the DAX Query to use as the string for the sales representative. It might look like this if it's coming from a variable:

     

    If you have trouble, I'd recommend the Power Automate forums rather than the Power BI forums for troubleshooting how to make the dynamic content work.

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Petanek333 ,

    Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.

     

    Best regards,
    Community Support Team_Binbin Yu

    • Petanek333's avatar
      Petanek333
      Icon for Helper III rankHelper III

      Hi Anonymous  I did not have time to try it yet. I hope to get to it in a couple of days and will respond hopefully with accepting as solution 🙂