Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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 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
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.
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |