MdxScript(Model) (38, 2) Error!

[Rupsayar]

Hello,

I'm new to Power BI and a big shout out to the community!

I'm facing a peculiar problem. Let me explain in layman's terms (as I am one!):

I have created a P&L dashboard, the data exported from .xls to the same. I have a date field [End Date] and a [Revenue] field. I wanted to create a month-over-month (MoM%) change in [Revenue] with respect to [End Date]. So I created a Bar-and-Line combo chart, with [Revenue] data as 'bars' and MoM% change as 'line'. I used the 'Quick Measures' feature and created a field [Revenue MoM%]. And I got what I wanted, see 'Revenue (MoM%)' figure below:

Now, when I used the date slicer to fine tune on (any) timeframe, I got the error:

 

Can anyone please help me resolve this?

[v-yuezhe-msft · Accepted solution]

@Rupsayar,

Regarding to the issue why the above error throws when using date slicer  to filter the chart, please review this similar thread.

In your scenario, please change the DAX of the measure by removing the IF statement. There is an example fo your reference.

SalesAmount MoM%  = 
IF(
	ISFILTERED('Calendar'[DateKey]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	VAR __PREV_MONTH =
		CALCULATE(
			SUM('Sales'[SalesAmount]),
			DATEADD('Calendar'[DateKey].[Date], -1, MONTH)
		)
	RETURN
		DIVIDE(SUM('Sales'[SalesAmount]) - __PREV_MONTH, __PREV_MONTH)
)

Change to:

SalesAmount MoM% = 

	VAR __PREV_MONTH =
		CALCULATE(
			SUM('Sales'[SalesAmount]),
			DATEADD('Calendar'[DateKey].[Date], -1, MONTH)
		)
	RETURN
		DIVIDE(SUM('Sales'[SalesAmount]) - __PREV_MONTH, __PREV_MONTH)

Regards,
Lydia

[Rupsayar]

Thanks. Solved.

[v-yuezhe-msft · Accepted solution]

@Rupsayar,

Glad to hear  the issue is solved, you can accept my reply as answer to close this thread.

Regards,
Lydia

[v-yuezhe-msft · Accepted solution]

@Rupsayar,

Regarding to the issue why the above error throws when using date slicer  to filter the chart, please review this similar thread.

In your scenario, please change the DAX of the measure by removing the IF statement. There is an example fo your reference.

SalesAmount MoM%  = 
IF(
	ISFILTERED('Calendar'[DateKey]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	VAR __PREV_MONTH =
		CALCULATE(
			SUM('Sales'[SalesAmount]),
			DATEADD('Calendar'[DateKey].[Date], -1, MONTH)
		)
	RETURN
		DIVIDE(SUM('Sales'[SalesAmount]) - __PREV_MONTH, __PREV_MONTH)
)

Change to:

SalesAmount MoM% = 

	VAR __PREV_MONTH =
		CALCULATE(
			SUM('Sales'[SalesAmount]),
			DATEADD('Calendar'[DateKey].[Date], -1, MONTH)
		)
	RETURN
		DIVIDE(SUM('Sales'[SalesAmount]) - __PREV_MONTH, __PREV_MONTH)

Regards,
Lydia