Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
alee5210
Helper II
Helper II

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.

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@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_0-1726577288900.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

 

DataInsights
Super User
Super User

@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_0-1726577288900.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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?

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.