Recently I had to get some data from a Power BI Dataset. At first, I started writing the DAX using the fantastic DAX Studio.

Then a thought occurred to me, what if I could get the DAX already written and change it to my requirement. This would save me a lot of time and effort. I love the quote from Patrick in Guy in a Cube “I am not lazy, I am efficient”

What I wanted to do was to get the DAX syntax and change it to

  • The first thing I did was I went into Power BI Desktop, clicked on View and then selected Performance analyzer
  • I then clicked on Start Recording
  • Within my Report page I changed the Yr-Month slicer to be 2020-Jan
  • In the Performance analyzer pane, I clicked Stop and then I clicked on Copy query
  • I now had my DAX query
  • I opened up DAX Studio, and connected to my PBIX File.
  • I then pasted in the DAX query below with an explanation of what this particular DAX query is doing
  • I then ran the query and got the expected results
  • The fun part was now I could modify this query to what I needed.
    • I renamed the VARIABLES to be more meaningful.
      • I added another Yr-Month to my DAX Query
        VAR FilterYrMonth =
        TREATAS (
            {
                DATE ( 2020, 1, 31 ),
                DATE ( 2020, 2, 29 )
            },
            'Date'[Yr-Month]
    • For my Table I removed the Target_by_Month, renamed “Cumulative_Sales” to “Cumulative Sales” and the IsGrandTotalRow
      VAR FilterMyTable =
        SUMMARIZECOLUMNS (
            'Date'[Yr-Month],
            FIlterYrMonth,
            "Sales", 'Order'[Sales],
            "CumulativeSales", 'Order'[Cumulative Sales]
        )
    • I also removed the TopN and Order By because I do not need it.
  • This is what my DAX looked like once updated.
    // DAX Query
DEFINE
    VAR FilterYrMonth =
        TREATAS (
            {
                DATE ( 2020, 1, 31 ),
                DATE ( 2020, 2, 29 )
            },
            'Date'[Yr-Month]
        )
    VAR FilterMyTable =
        SUMMARIZECOLUMNS (
            'Date'[Yr-Month],
            FIlterYrMonth,
            "Sales", 'Order'[Sales],
            "CumulativeSales", 'Order'[Cumulative Sales]
        )
EVALUATE
FilterMyTable
  • This was exactly what I was after and I could see the results in DAX Studio

Summary

It definitely took me longer to write this blog post than what it did to quickly get the DAX query, put it into DAX Studio and then make the required changes.

I hope that you found this interesting and could possibly use it.

Any questions or comments please let me know. Thanks for reading.