topic Need help on Dax Command to Pull Data from Analysis Service Cube in DAX Commands and Tips

Need help on Dax Command to Pull Data from Analysis Service Cube

prabhatnath — Tue, 30 Apr 2024 02:14:43 GMT

I have 2 Tables in Analysis Service and I need help for a Dax query to run on Dax Studio to pull Data including executing the measure.

Table-1: 'Create Date' is a Calendar Table. Columns: DateId, CalendarDate, FiscalYear, FiscalQtr, CalendarMonth, Calendar Month and Many other columns.

Sample Data:

Table-2: 'OpsView' is the table with Measures.

Columns: DateId, ReportingMonth, Region, RI

Measures: OpsRIRolling, RIRateRolling.

The ReportingMonth is Date type.

In Power BI we have a Line and Staked Column Chart that has:

X-axis = 'Create Date'[CalendarMonth] (formated as below)

Y-axis = 'OpsView'[RI]

Line Y-axis = 'OpsView'[RIRateRolling]

Sample Data in Table visual:

And the report has below Filters:

'OpsView'[Region] = ALL .

'Create Date'[Calendar Month] = Date Slicer for last 12 Months.

I need help to build a DAX query to execute in DaxStudio to get exact data similar as in the table visual.
Like: MonthName, RI, OpsRIRolling, RIRateRolling, FiscalYear

It should be group By 'Create Date'[CalendarMonth] as in the table visual.

Also with that data if we can include region that will be good so in my data I can filter the result per region.

Please help.

Thanks,

Prabhat

Re: Need help on Dax Command to Pull Data from Analysis Service Cube

lbendlin — Tue, 30 Apr 2024 12:02:18 GMT

Connect to the cube live. Add the required data elements to a table visual. Disable totals. Run Performance Analyzer and grab the DAX query for that visual.

Re: Need help on Dax Command to Pull Data from Analysis Service Cube

Anonymous — Wed, 01 May 2024 07:48:22 GMT

lbendlin Thanks for your contribution on this thread.

Hi prabhatnath ,

You can apply the below DAX query in DAX Studio:

EVALUATE SUMMARIZE ( FILTER ( 'Create Date', 'Create Date'[CalendarMonth] >= DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), 1 ) && 'Create Date'[CalendarMonth] <= TODAY () ), 'Create Date'[CalendarMonth], "RI", CALCULATE ( SUM ( 'OpsView'[RI] ) ), "OpsRIRolling", CALCULATE ( SUM ( 'OpsView'[OpsRIRolling] ) ), "RIRateRolling", CALCULATE ( SUM ( 'OpsView'[RIRateRolling] ) ), "FiscalYear", 'Create Date'[FiscalYear], "Region", 'OpsView'[Region] ) ORDER BY 'Create Date'[CalendarMonth]

Best Regards