Forum Discussion

codyraptor's avatar
codyraptor
Icon for Resolver I rankResolver I
4 years ago
Solved

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 e...
Distinct Counts
Duplicates
SUMMARIZE
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
codyraptor
Icon for Resolver I rankResolver I
4 years ago

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
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.