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

     

     

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.

 

alee5210's avatar
alee5210
Helper II
1 year ago

It did not work perfectly for me, but it helped me go down a path to find the answer I was after.