Forum Discussion

Wilsonr's avatar
Wilsonr
Helper I
8 years ago

SELECTCOLUMNS - Issue when Using a Measure

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

Need Help

5 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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's avatar
      Wilsonr
      Helper I
      8 years ago

      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

    • Wilsonr's avatar
      Wilsonr
      Helper I
      8 years ago

      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's avatar
        Wilsonr
        Helper I
        8 years ago

        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