Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!!
Solved! Go to 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] ) )
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.
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.
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.
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 |
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.
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.
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.
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
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |