cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
karo
Helper V
Helper V

Context in DAX measure

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?

 

2020-12-15_16-52-52.jpg2020-12-15_20-43-31.jpg

 

 

 

Regards,

Karo

 

 

1 ACCEPTED SOLUTION

@karo 

 

Ok. Here is one way.

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

Slicer.JPG

new model.JPG

 

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.

matrix.JPG

 

and you will get the following:

all sel.JPGselect.JPG

 

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

13 REPLIES 13
v-easonf-msft
Community Support
Community Support

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

v-easonf-msft
Community Support
Community Support

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
    )

 

 

99.png

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

 

Best Regards,
Community Support Team _ Eason

@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

v-easonf-msft
Community Support
Community Support

Hi, @karo 

Measure [all] is composed of three other measures.

Is it convenient to show its code?

 

Best Regards,
Community Support Team _ Eason

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

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

 

 

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

PaulDBrown
Super User
Super User

@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])






Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

@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:

model.JPG

 

result.JPG

 

Using the option under "Values" the Format pane and:
Values o rows.JPG

 

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]
)




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

 

@karo 

 

Ok. Here is one way.

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

Slicer.JPG

new model.JPG

 

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.

matrix.JPG

 

and you will get the following:

all sel.JPGselect.JPG

 

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

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

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors