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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

DAX Calculation unique Count

Hi Team,

 

I am trying to create report for Retail Analytics.  I have a table with Columns -

Phone Number, Invoice Date which has duplicates.

 

With Invoice Date I am getting No. of visits (Calculated Measure) with the formula  CALCULATE(COUNT(CustomerInvoice[Invoice Date]),ALLEXCEPT(CustomerInvoice, CustomerInvoice[Invoice Date],CustomerInvoice[Phone Number],CustomerInvoice[val])

 

Another Calculated Measure Fraud or Valid - >Fraud or Valid = (IF([No Of Visits] > 3, "FRAUD", "VALID"))

I need to show the number of Fraud Counts in a card visual.

 

PYerror.PNG

 

Since I am having duplicate Phone Number or Invoice Date. I am not getting the Fraud Count correctly.

 

Kindly help me on getting the DAX query.

 

Regards,

Gowrishankar

 

Regards,

Gowrishankar

 

1 ACCEPTED SOLUTION

@Sidhartha_1992

 

Try this one

 

Measure =
COUNTX (
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( TableName ),
            TableName[Phone Number],
            TableName[Invoice Date]
        ),
        [Fraud or Valid] = "Fraud"
    ),
    1
)

Regards
Zubair

Please try my custom visuals

View solution in original post

9 REPLIES 9
Greg_Deckler
Super User
Super User

I'm not sure I am following this, so are you saying that you only want the 123456 phone number counted once as a fraud instead of multiple times? If that is the case, then perhaps you could take the DISTINCTCOUNT of your phone numbers and subtract that from your COUNT of phone numbers and that would give you how many duplicates you have and then just subtract that from your fraud count?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

No,Sir i want the phone number 123456 to be counted as two.

The solution you provided gets me the distinct count of FRAUD numbers but i want the overall count of FRAUD numbers which should be 4 for the table provided above but your formula is giving the count as 3

@Sidhartha_1992

 

Try this one

 

Measure =
COUNTX (
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( TableName ),
            TableName[Phone Number],
            TableName[Invoice Date]
        ),
        [Fraud or Valid] = "Fraud"
    ),
    1
)

Regards
Zubair

Please try my custom visuals

Thanks a lot Zubair,it worked for me 🙂

@Sidhartha_1992

 

I noticed someone else started this post Smiley Tongue

 

Are you also @gowrishankar65 ?

 

 


Regards
Zubair

Please try my custom visuals

Actually @gowrishankar65 is my senior,we both were trying to find out the solution to this problem so he posted it  🙂

Hi @Zubair_Muhammad,

 

We both are working on the same project. 😉

 

Thanks Zubair 🙂

 

Regards,

Gowrishankar

@gowrishankar65

 

This Measure should work!

Fraud Number =
CALCULATE (
    DISTINCTCOUNT ( CustomerInvoice[Phone Number] ),
    FILTER ( CustomerInvoice, [Fraud or Valid] = "Fraud" )
)

Good Luck! Smiley Happy

 

DistinctCount Filter Measure.png 

 

Zubair_Muhammad
Community Champion
Community Champion

@gowrishankar65

 

Try with following

 

Measure =
COUNTX (
    FILTER ( ALLSELECTED ( TableName[Phone Number] ), [Fraud or Valid] = "Fraud" ),
    1
)

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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