Measure get different result if slicer is "All" or maual selection of each items

[Anonymous]

Hi,

I have been using Power BI for a while now, and i'am now stucked:

My dataset contains forecasted figures. i want to be able to compare the forecast for June i had last week with the forecast i have this week. (i will talk of extract_date)

Now the weird thing: Depending on the way i select my extract date in the slicer, i do not got the same result:

If i select nothing, i have the right result:

Nothing Selected

If i use the option "select All", i have the right result:

Select All

If i select the options myself, the result is false:

Manuel selection

Any clue where this could come from?

I check the formulas several time, verified also the options in the calculate expression of my measures (i use calculate / Filter / Allexcept) --> the results are correct when i select all, of none, but it get weird only when i select each value
Thank you for your help!

[Anonymous · Accepted solution]

I finally found a solution to my problem.

Although it do no ansewer exactly why the Slicers was being weird, i found a solution to display what a wanted.

The formula i build makes a distinction if it should calculate the total, or a row of my matrix. Based on that, made two possible results:

1- if it is the total, using allselected, and filtering on the Extract Date

2- if it is a row f my matrix: using all selected, and filtering on the extract date, and the dimension of my row.

Tank you all for your ideas !

BR

[Anonymous]

it looks like using allselected instead of allexcept solve the problem for the total, but them i can not use a matrix visual to see the result on various dimensions of my dataset.

(i double and triple cheked, and all the colmns are well written in the allexcept...)

Any clue on how exactly the funtion allexcpet is working?

[v-lili6-msft]

hi  @Anonymous 

I would suggest you create a Date Dimension Table and create the relationship with 'sheet1' table, then use this Date Dimension Table in the formula and visual.

Please refer to this document:

https://radacad.com/do-you-need-a-date-dimension

Regards,

Lin

[Pragati11]

HI @Anonymous ,

Are there any other values like BLANKS or NULLs in your Extract Date column?

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[Anonymous]

Hi,

no, there is no blank or null in my extract date column

[Pragati11]

Hi,

Can you share your .pbix file or may be sample data? It's hard to identify the issue.

Thanks,

Pragati

[Greg_Deckler]

That's highly unusual. What does the source data look like behind this and what are the measure formulas? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Hi @Greg_Deckler 

Thanks for your quick reply.

The datasource behind is a huge table, without any relationships.

The formulas look like:

Value_offset =

var last_date = CALCULATE(MAX(Sheet1[Extract date]);FILTER(ALLSELECTED(Sheet1);Sheet1[Extract date]<MAX(Sheet1[Extract date])))

Return

CALCULATE(SUM(Sheet1[Value]);FILTER(ALLEXCEPT(Sheet1;Sheet1[date]);Sheet1[Extract date]=last_date))

However, when a created this formula in a dummy_data power BI, it gives the right result:
could the size of my dataset (>13mio rows) cause this kind of problems ?