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

[prabhatnath]

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

[v-yiruan-msft · Accepted solution]

@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

[lbendlin]

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.