SELECTCOLUMNS - Issue when Using a Measure

[Wilsonr]

I am using SELECTCOLUMNS create a new table called "My_Table" from an existing table called "Dedicated_Measures". I want to recover the "Week", "Product" and "Value" columns for a given week, i.e. 2018W34. I already have a measure in table "Dedicated_Measures" to recover the Value for this week which is called "Value 2018W34" which is defined as follows:

Value 2018W34 =
    CALCULATE(
        SUM(Dedicated_Measures[Value]),
        FILTER(ALL(Dedicated_Measures[Week]),Dedicated_Measures[Week] = "2018W34")
    )

I then created my new table using this measure:

My_Table =
    SELECTCOLUMNS(
        Dedicated_Measures,
        "Week", [Week],
        "Product", [Product],
        "Value 2018W34", Dedicated_Measures[Value 2018W34]
    )

My results gave me what I expected - values for Week = 2018W34, and no values for all other weeks. Filtering the results on 2018W34 and a single Product (for simplicity), I got the following results:

However the sum across all the Products for 2018W34 didn't look correct. So I added another column to my new table which uses the original "Value" column in Dedicated_Measures. Once again filtering the results on 2018W34 and a single Product (for simplicity), I got the following results:

I cannot figure out why, when using a measure, the results have doubled?! These two rows exist in my original data, and they both contain a value of 1506.93. Why when using a Measure do the values double? If I build the measure inside the DAX code which creates my new table, the results are correct.

Thanks in advance,

Ron

[v-jiascu-msft]

Hi Ron,

Try to remove "ALL" of "Value 2018W34", which remove the context of Week. 

Value 2018W34 =
    CALCULATE(
        SUM(Dedicated_Measures[Value]),
        FILTER(ALL(Dedicated_Measures[Week]),Dedicated_Measures[Week] = "2018W34")
    )

Best Regards,

Dale

[Wilsonr]

Dale,

I made the edit you suggested and it worked. I am using the ALL because I have slicers attached to my Dedicated_Measures in order to select the Week.

Also, while SELECTCOLUMNS gives me the undesired result when using the ALL(), replacing it with SUMMARIZE works:

Unfortunately, I don't understand why using ALL is a problem? Can you explain?

Thanks in advance,

Ron

[Wilsonr]

Dale,

I spoke too soon in my reply below - removing the ALL() makes no difference. So while SELECTCOLUMNS doesn't work, SUMMARIZE works.

I wanted to attach the file, but can't find a way to do so.

Thanks in advance,

Ron

[v-jiascu-msft]

Hi Ron,

You can upload the file to the cloud service like OneDrive, GoogleDrive then share the download link here. 

It seems something wrong with your data structure.

Best Regards,

Dale

[Wilsonr]

Dale,

Thanks. I went even further and removed all of the data in table Dedicated_Measures except for the desired Week and Product via the MySQL query which generates this table. So the source table now looks like this:

while the SELECTCOLUMNS produces this:

How can this be possible?

Ron