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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
codyraptor
Resolver I
Resolver I

Count/sum without duplicate rows and define which row is accurate

This is probably super easy...just haven't dealt with it in DAX.  How do I go about getting the correct count/sum when I have duplicate dimensions and you need to specify which row to group by. For example...if you have a distinct ID that has a status of both Active and Cancel in the dataset...and Active should be the correct status when filtering/slicing/visualize by status...how do you count ID's correctly? I need to be able to specify the correct status. I normally would do this in SQL by creating a subquery of ID's and use a case statement...but wasn't sure how to do this or if this is the most optimized approach in dax. Thanks!!

1 ACCEPTED SOLUTION

Hi @codyraptor ,

Please refer to my pbix file to see if it helps you. I also use Direct Query mode.

Create measures.

Measure 1 = IF(SELECTEDVALUE('table$'[STATUS])="Open",1,0)
Measure_2 =
VAR maxx_ =
    MAXX ( ALLEXCEPT ( 'table$', 'table$'[ID] ), [Measure 1] )
RETURN
    IF ( maxx_ = 1, "OPEN", SELECTEDVALUE ( 'table$'[STATUS] ) )

vpollymsft_0-1652841460742.png

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
codyraptor
Resolver I
Resolver I

I'm guessing I just have to create it in sql prior to coming to PBI...and then create multiple agg tables...one at the ID Lvl Status...and another one at the lower granularity.  Let me know if this is how you would do it.

codyraptor
Resolver I
Resolver I

Here's a sample of data...  My issue is...I want to show the correct grouping for 'Status'.  If a dispID has an open status...I want the entire disp to = Open.  My counts below should show 2 Open and 1 Cxl.  

IDStatus
123CXL
123Open
124Open
125CXL
125CXL

 

I know how to do this by creating a calculated column.  However, I'm trying to make use of an agg table using a composite model and DQ doesn't allow these types of calculations to take place.  I either have to do it in 'M'...or create seperate Agg tables...etc..  In a perfect world...I would want to create my table like the following with the added ID level status..

IDID Lvl StatusStatus
123OpenCXL
123OpenOpen
124OpenOpen
125CxlCXL
125CxlCXL

Hi @codyraptor ,

Please refer to my pbix file to see if it helps you. I also use Direct Query mode.

Create measures.

Measure 1 = IF(SELECTEDVALUE('table$'[STATUS])="Open",1,0)
Measure_2 =
VAR maxx_ =
    MAXX ( ALLEXCEPT ( 'table$', 'table$'[ID] ), [Measure 1] )
RETURN
    IF ( maxx_ = 1, "OPEN", SELECTEDVALUE ( 'table$'[STATUS] ) )

vpollymsft_0-1652841460742.png

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Your data is missing an index column.

Not sure I follow.  Why do I need an index column?  The sample dataset is the result of a sql query from a data warehouse.  ID is the distinct order ID...the status is the status of the items on the order ID.  I just need to apply business logic to create an 'order level status'.  Can you point me in the right direction as to what an 'index' would help do?

Neither Power Query nor DAX have a concept of "row number". They don't know which row of 123 to consider "first"  versus "second", for example.

v-rongtiep-msft
Community Support
Community Support

Hi @codyraptor ,

Can you provide a simple PBIX file for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures. I look forward to your response.

 

How to Get Your Question Answered Quickly 

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

lbendlin
Super User
Super User

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors