Calculate measure based on same date and slicer

[Milanjj]

Hi there,

I am trying to create a measure which updates itself based on the selection of a slicer.

This is my table:

Right now PRday is a sum of PR per day, so over all environments:

PRday = CALCULATE(SUM(TimeLine[Paved Road]), ALLEXCEPT (TimeLine, TimeLine[Date]))

What I try to do is to change this measure to update based on the selection of a multiselect Slicer on the Environment.

Example: If NONPROD and DEV are seected in the Slicer, PRday should display 25827 for 19 January 2023, if all Environments are selected the value should be 52040 and if only DEV is selected it should be 9903, per row!

Thank you for any hints!

Kind regards Milan

[Milanjj · Accepted solution]

Solved it myself 🙂

The new measure which solves it is:

NewPR = 
VAR SelectedEnvs = ALLSELECTED ( TimeLine[Environment] )
RETURN
    CALCULATE (
        SUM(TimeLine[Paved Road]), 
        REMOVEFILTERS ( ),
        VALUES ( TimeLine[Date] ),
        SelectedEnvs
    )  

[v-yiruan-msft]

Hi @Milanjj ,

What you are creating is a calculated column or measure? If what you created is a calculated column, it will not change according to the user interaction(slicer, filter, column selections etc.) in the report as the value of a calculated column is computed during data refresh and uses the current row as a context... Please review the following links about the difference of calculated column and measure...

Calculated Columns and Measures in DAX

Calculated Columns vs Measures

So if you want to get the dynamic value base on the selected values, you need to create a measure. I created a sample pbix file(see the attachment), please check if that is what you want.

If the above one can't help you, could you please provide more raw data in your table ‘Timeline’(exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Milanjj]

Hi @v-yiruan-msft 

what you write sounds really promissing, where can I find the pbix you attached?

[v-yiruan-msft]

Hi @Milanjj ,

It's my mistake, I forgot to attach the pbix file in my previous post. I just attach it this time, please check it. Any comment or problem, please feel free to let me know.

Best Regards

[Milanjj]

unfortunately this also does not solve the problem.

What I need is a conditional sum per row in the PRday measure like this:

• If no filter is selected in PRday give in each row the sum of Paved Road per day (which is a sum over al three environments per day)
• If a filter on Environment is active on the slicer update the PRday calulation per row such that it includes only the Environments selected in the Environment Slicer

Hope that this is making it clearer. I have changed the pbix to show the two elements which I need only (but can not upload pbix!?!) here is a picture:

What I am missing is a measure to calulate PRday in the way I layed out above.

[pratyashasamal]

Hi @Milanjj ,
Try this Dax measure :-

PRday = CALCULATE(SUM(TimeLine[Paved Road]), ALLEXCEPT (TimeLine, TimeLine[Date]),ALLSELECTED(TimeLine[Environment]))

Thanks,
Pratyasha Samal
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

[Milanjj]

Hi @pratyashasamal 

thank for the suggestion. Unfortunately it does not work, your DAX measure gives exactly the same result as my initial one above. Not sure why. Do you have another idea?

[pratyashasamal]

Hi @Milanjj ,
Please this as well 

PRday = CALCULATE(SUM(TimeLine[Paved Road]),ALLSELECTED(TimeLine[Environment]))

Thanks,
Pratyasha Samal

[Milanjj]

Hi @pratyashasamal 

this also does not work, as it is no longer summing per day, it is only returning the value from PR

KR

Milan

[pratyashasamal]

Hi @Milanjj ,
Keep the date field to day granularity it will automatically sum to day level .Then use ALLSELECT function only.
Thanks ,
Pratyasha Samal

[pratyashasamal]

Hi @Milanjj ,
can you try splitting into measures .

PRday = CALCULATE(SUM(TimeLine[Paved Road]), ALLEXCEPT (TimeLine, TimeLine[Date]))

PRday2 = CALCULATE([PRday],ALLSELECTED(TimeLine[Environment]))

 and then add PRday2 to your visual.
Thanks,
Pratyasha Samal
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

[Milanjj]

still not working... also gives same result as PRday