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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
MasterSonic
Helper IV
Helper IV

Count unique per other columns

Hello,

CodePlanStatus
101BuyReady
102SellReady
102SellReady


I would like to count unique code by Status and Planm but I have duplicates in Code column.

So on Report,  I could drop Plan and "Count of Status" showing 1 for Plan SELL, based on table above.



I have this measurment but it doesn't do the job, as is showing 2, counting Code per plan ONLY, without looking at Status

Measur1 = CALCULATE(DISTINCTCOUNT('Table'[Code]),ALLEXCEPT('Table'[Plan]))

Is there any way to perform calculaction please?
1 ACCEPTED SOLUTION

not sure if i get you, try this

Measure2 = 

COUNTROWS(
    SUMMARIZE(
        TableName, 
        TableName[Plan], 
        TableName[Code]
    )
)

View solution in original post

4 REPLIES 4
FreemanZ
Super User
Super User

hi   @MasterSonic 

try like:
 
Measure2 = 
COUNTROWS(
    SUMMARIZE(
        TableName, 
        TableName[Plan], 
        TableName[Status]
    )
)

Hi,
thanks but It does not give the wanted result.

lets extend my table

CodePlanStatus
101BuyReady
102SellReady
102SellReady
103SellReady


Now it should showing  col=Plan value=Sell  should be 2
As we have code duplicated.

I would like to count Code per Plan where value in column Status is Ready, but do not count duplicated Codes. Maybe this will help

not sure if i get you, try this

Measure2 = 

COUNTROWS(
    SUMMARIZE(
        TableName, 
        TableName[Plan], 
        TableName[Code]
    )
)

Thank You for help I have added below and I got what wanted.

Measure2 = 

COUNTROWS(
    SUMMARIZE( FILTER( Table,Table[Status] = "Ready"),
        
        TableName[Plan], 
        TableName[Code]
    )
)

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.