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,...
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

     

     

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