Forum Discussion
Resolver ICount/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!!
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] ) )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.
8 Replies
- lbendlin
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 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.
codyraptorHere'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.
ID Status 123 CXL 123 Open 124 Open 125 CXL 125 CXL 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..
ID ID Lvl Status Status 123 Open CXL 123 Open Open 124 Open Open 125 Cxl CXL 125 Cxl CXL - lbendlin
Your data is missing an index column.
- codyraptor
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?
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] ) )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.
- codyraptor
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.
