topic Re: Context in DAX measure in DAX Commands and Tips

Context in DAX measure

karo — Tue, 15 Dec 2020 20:03:43 GMT

Hi,

I would like to obtain symilar effect for table like in case of dynamic measure selector for charts. So far I was able to obtain the view inserted below, however I need to find a way to add Actual data for past months to Forecast as well (without making a copy of Actuals). And I am strugling with filter/row context. Anyone can help, please?

Regards,

Karo

Re: Context in DAX measure

PaulDBrown — Wed, 16 Dec 2020 18:04:09 GMT

karo

You need to create a measure to include actuals if the filter context if before the current month, or the forecast value if it is after.

Something along the lines of:
Combined =
VAR YM = YEAR(MAX('Calendar '[Date])) * 100 + MONTH(MAX('Calendar '[Date]))

VAR CurrentYM = YEAR(TODAY()) *100 + MONTH(TODAY())
RETURN
SUMX(Calendar, IF(YM < CurrentYM, [your actuals measure], [your forecast measure])

Re: Context in DAX measure

karo — Thu, 17 Dec 2020 07:59:55 GMT

Hi PaulDBrown , thank you for reply, however it is not solving my issue. Still context (please refer to 'Table'[Type] from my screens) is an issue.

Regards,

Karo

Re: Context in DAX measure

v-easonf-msft — Thu, 17 Dec 2020 10:18:29 GMT

Hi, karo

Measure [all] is composed of three other measures.

Is it convenient to show its code?

Best Regards,
Community Support Team _ Eason

Re: Context in DAX measure

PaulDBrown — Thu, 17 Dec 2020 11:03:45 GMT

karo

Is there a reason you need the slicer for type on this visual?

You can create a matrix with the values on rows like this:

Using the option under "Values" the Format pane and:

The measures I used to create the Measure to show sales or forecast:

1) The if measure to select sales or forecast:

calc sales or forecast = VAR YM = YEAR(MAX('Calendar Table'[Date])) * 100 + MONTH(MAX('Calendar Table'[Date])) VAR CurrentYM = YEAR(TODAY()) *100 + MONTH(TODAY()) RETURN IF(YM < CurrentYM, [Sum Sales], [Sum Forecast])

2) The final measure:

Combined Sales and Forecast = SUMX ( SUMMARIZE ( 'Calendar Table', 'Calendar Table'[YearMonth], "Combined", [calc sales or forecast] ), [Combined] )

Re: Context in DAX measure

karo — Thu, 17 Dec 2020 11:31:36 GMT

Hi PaulDBrown Yes, there is 🙂 I am trying to obtain similar effect for table like in case of dynamic measure selector for charts (please refer to https://visualbi.com/blogs/microsoft/powerbi/dynamic-measure-selection-power-bi/ as an example) .

Regards,

Karo

Re: Context in DAX measure

karo — Thu, 17 Dec 2020 11:46:50 GMT

Hi v-easonf-msft , Yes and sure. I have prepared dummy .pbix based on Power BI Sample Data. In my real scenario Budget,Actuals and Forecast data are comming from different systems. What is the best way to share the pbix file ?

Regards,

Karo

Re: Context in DAX measure

PaulDBrown — Thu, 17 Dec 2020 16:29:22 GMT

karo

Ok. Here is one way.

with a Type table which is disconnected from other tables in the model:

Create new measure as follows (I'm referencing the measures posted in my previous reply):

Filtered measures = SWITCH ( TRUE (), MAX ( 'Type Selection'[Type] ) = "Sales", [Sum Sales], MAX ( 'Type Selection'[Type] ) = "Target", [Sum Target], [Combined Sales and Forecast] )

Add the "Type" column from the Type selection table to the row bucket of a matrix, the year and month columns from the calendar table as columns, and the [Filtered measures] measure to the "Values" bucket.

and you will get the following:

Re: Context in DAX measure

v-easonf-msft — Fri, 18 Dec 2020 01:38:08 GMT

Hi, karo

For data security considerations, it is recommended that you upload the file to Onedrive for Bussiness and share the link.

Best Regards,
Community Support Team _ Eason

Re: Context in DAX measure

karo — Fri, 18 Dec 2020 07:41:44 GMT

v-easonf-msft I have shared the link with you via private message. Thanks a lot for looking into my case!

Re: Context in DAX measure

v-easonf-msft — Fri, 18 Dec 2020 09:37:09 GMT

Hi, karo

Change your measure [All] as below:

All2 = VAR A = IF ( ISBLANK ( [Forecast] ), VAR tab = SUMMARIZE (FILTER( ALL ( Actuals ), [Date]>=DATE(2020,09,01)),Actuals[Date], [Value] ) RETURN SUMX ( FILTER ( tab, YEAR ( [Date] ) = SELECTEDVALUE ( 'Calendar'[Date].[Year] ) && MONTH ( [Date] ) = SELECTEDVALUE ( 'Calendar'[Date].[MonthNo] ) ), [Value] ), [Forecast] ) RETURN IF ( HASONEVALUE ( 'Table'[Type] ), SWITCH ( SELECTEDVALUE ( 'Table'[Type] ), "Actuals", [Actuals], "Budget", [Budget], "Forecast", A ), [Actuals] + [Budget] + A )

If it doesn't meet your requirement,please feel free to let me know.

Best Regards,
Community Support Team _ Eason

Re: Context in DAX measure

karo — Fri, 18 Dec 2020 14:28:50 GMT

PaulDBrown Big, big thank you! I replicated your steps and it seems that it can be a solution.

Re: Context in DAX measure

karo — Fri, 18 Dec 2020 14:31:31 GMT

v-easonf-msft thank you so much! I will try to replicate your DAX in my report based on "real" data and in case of doubts I will go back

Re: Context in DAX measure

v-easonf-msft — Mon, 21 Dec 2020 08:53:06 GMT

Hi, karo

You’re welcome.

If you have solved problem, please mark the solution to close this thread.

If you haven't, please feel free to ask.

Best Regards,
Community Support Team _ Eason