Forum Discussion
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.
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
Try this one
Measure = COUNTX ( FILTER ( SUMMARIZE ( ALLSELECTED ( TableName ), TableName[Phone Number], TableName[Invoice Date] ), [Fraud or Valid] = "Fraud" ), 1 )
9 Replies
Try with following
Measure = COUNTX ( FILTER ( ALLSELECTED ( TableName[Phone Number] ), [Fraud or Valid] = "Fraud" ), 1 )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?
This Measure should work!
Fraud Number = CALCULATE ( DISTINCTCOUNT ( CustomerInvoice[Phone Number] ), FILTER ( CustomerInvoice, [Fraud or Valid] = "Fraud" ) )Good Luck! :smileyhappy:
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
Try this one
Measure = COUNTX ( FILTER ( SUMMARIZE ( ALLSELECTED ( TableName ), TableName[Phone Number], TableName[Invoice Date] ), [Fraud or Valid] = "Fraud" ), 1 )
