Forum Discussion

alee5210's avatar
alee5210
Helper II
1 year ago
Solved

Power BI Measure That Is Like 'Over Partition' used in SQL

Hi All, I am trying to create a measure that can get me a single value that is similar to over partition in SQL.

 

The logic I am after is like the below.

I have the columns Store, Sale A, Sale B, Sale C. I have included the DistCountA, DistCountB, DistCountC and Overall columns so I can more easily explain how to get the final value at the end but these columns are not given to me. Firstly, I am trying to get the distinct count of Sales for each store.

 

So for Store X, in the Sale A column there is only a single unique value. For Store Y in the Sale A column there are 2 unique values and both these values ends up in the DistCountA Column for each store. This happens also for DistCountB and DistCountC and the different stores. If the number of distinct values is equal to 1 for DistCountA, DistCountB and DistCountC, then the final result in the Overall column is 1, otherwise it is 0.

 

I then want to sum all the values in the Overall column, in this case, it is 2. I want a measure that will return the value 2 in a card. Is this possible using a measure only? 

 

Store Sale A Sale B Sale C DistCount A DistCount B DistCount C Overall
X 1 5 9 1 2 1 0
X 1 6 9 1 2 1 0
Y 2 4 8 2 1 1 0
Y 3 4 8 2 1 1 0
Z 4 1 7 1 1 1 1
Z 4 1 7 1 1 1 1

 

The reason I want it to be a measure is that there are additional columns like date that users can filter on and that will change the results. For this example, I have omitted it since I want to give a more simplified version.

Need Help
Show and Tell
Tips and Tricks
  • DataInsights's avatar
    DataInsights
    1 year ago

    alee5210,

     

    Try this measure:

     

    Sum Overall = 
VAR vBaseTable =
    ADDCOLUMNS (
        Sales,
        "DistinctCountA", CALCULATE ( DISTINCTCOUNT ( Sales[Sale A] ), ALLEXCEPT ( Sales, Sales[Store] ) ),
        "DistinctCountB", CALCULATE ( DISTINCTCOUNT ( Sales[Sale B] ), ALLEXCEPT ( Sales, Sales[Store] ) ),
        "DistinctCountC", CALCULATE ( DISTINCTCOUNT ( Sales[Sale C] ), ALLEXCEPT ( Sales, Sales[Store] ) )
    )
VAR vFilterTable =
    FILTER (
        vBaseTable,
        [DistinctCountA] = 1
            && [DistinctCountB] = 1
            && [DistinctCountC] = 1
    )
VAR vResult =
    COUNTROWS ( vFilterTable )
RETURN
    vResult

     

     

6 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    1 year ago

    alee5210,

     

    Try this measure:

     

    Sum Overall = 
VAR vBaseTable =
    ADDCOLUMNS (
        Sales,
        "DistinctCountA", CALCULATE ( DISTINCTCOUNT ( Sales[Sale A] ), ALLEXCEPT ( Sales, Sales[Store] ) ),
        "DistinctCountB", CALCULATE ( DISTINCTCOUNT ( Sales[Sale B] ), ALLEXCEPT ( Sales, Sales[Store] ) ),
        "DistinctCountC", CALCULATE ( DISTINCTCOUNT ( Sales[Sale C] ), ALLEXCEPT ( Sales, Sales[Store] ) )
    )
VAR vFilterTable =
    FILTER (
        vBaseTable,
        [DistinctCountA] = 1
            && [DistinctCountB] = 1
            && [DistinctCountC] = 1
    )
VAR vResult =
    COUNTROWS ( vFilterTable )
RETURN
    vResult

     

     

    • alee5210's avatar
      alee5210
      Helper II
      1 year ago

      I've tried this and it seems to partially work but I'm not sure why.

      When I put this part into a measure and add it to a table or card, it works flawlessly

      distinctcountA = CALCULATE ( DISTINCTCOUNT ( Sales[Sale A] ), ALLEXCEPT ( Sales, Sales[Store] ) )

      When I then add it to vFilterTable it gives wildly different results. Is it because I'm filtering and this is not meant to be filtered?

      • DataInsights's avatar
        DataInsights
        Super User
        1 year ago

        alee5210,

         

        Would you be able to provide more details about the issue and expected result? I created the solution based on your requirement below:

         

        I want a measure that will return the value 2 in a card.

         

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    Would you be amenable to changing the structure of the table using Power Query i.e. converting the first 4 columns into just 3 - Store, Sale and Product?  If you are OK with this approach, please post back.

    • alee5210's avatar
      alee5210
      Helper II
      1 year ago

      Hi Anish, no i cannot change the table structure for this query. 

      I have tried a different approach based on some work by DataInsights but it did not work.

      I tried to create 3 measures, one for A - C. I've got additional fields, not just store that I want to group by in the below but I only posted store to keep things simple.

       

       

      DistinctCountA = CALCULATE ( DISTINCTCOUNT ( Sales[Sale A] ), GROUPBY( Sales, Sales[Store], Sales[Field2]... ) )
      DistinctCountB = CALCULATE ( DISTINCTCOUNT ( Sales[Sale B] ), GROUPBY( Sales, Sales[Store], Sales[Field2]... ) )
      DistinctCountC = CALCULATE ( DISTINCTCOUNT ( Sales[Sale C] ), GROUPBY( Sales, Sales[Store], Sales[Field2]... ) )

       

       

       

      In a table or card, this gives me the correct result for DistinctCountA/B/C.

       

      I then tried to create a measure after this to get the Overall column like the below.

       

       

      Overall = 
if(DistinctCountA = 1 && DistinctCountB  = 1 && DistinctCountC =1, 1, 0)

       

       

      When I did this, did not work properly. In the table, each row would be a 1, but the total would be 0 at the bottom of the table or in a card so I am stuck at this step