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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
prasadpatsa
Helper I
Helper I

Total not working for DISTINCTCOUNT when used matrix

Hello,

 

I am having an issue with TOTALS when used DATEDIFF in a matrix. I followed the links that dealt with these kind of issues by using 'HASONEVALUE' filter but its not working out for me.

 

Need: I will have to do DISTINCTCOUNT of  card_numbers in the last 30 days and show that as matrix with totals and also as a KPI.

 

Used: I am using the below calculations:

 

Method-1:

unique_card_cy =
var max_date = MAX(dim_date[date])
var countof = DISTINCTCOUNT(fleet_core_master_card[card_number])
return
IF( DATEDIFF( SELECTEDVALUE(dim_date[date]),max_date, day) <= 30, countof, 0)
 
Method-2:
TESTING_unique_card_cy =
var max_date = MAX(dim_date[date])
var countof = DISTINCTCOUNT(fleet_core_master_card[card_number])
return
CALCULATE(countof, DATESBETWEEN(dim_date[date],SELECTEDVALUE(dim_date[date])-30, SELECTEDVALUE(dim_date[date])))
 
In both the ways, I am getting the same result. Row level values are correct but not at the totals level. Look at the August numbers.
(2867+3960+54787+5009) = 66,623 but the total showing as 63,677.
 
prasadpatsa_0-1672157158853.png

 

 
Any advise would be appreciated! Thank you
 
4 REPLIES 4
prasadpatsa
Helper I
Helper I

Hi @AlexisOlson

 

Thank you for the response. Looking thorugh the link you shared, I understood that distinctcount is not an additive measure however given the requirement, it is such a simple requirement for anyone who would like to get a count.

Dont I have any alternative way to achive this?

 

Please advise.

If you want to force it to be additive, then you need to iterate over whatever dimension you have on the rows. Note that forcing the total to be additive means that the total may change depending on what dimension you use.

 

The typical way to sum the distinct count of ColB iterating over the dimension ColA looks something like this:

SUMX (
    VALUES ( Table1[ColA] ),
    CALCULATE ( DISTINCTCOUNT ( Table2[ColB] ) )
)

 

You may also want to check out @Greg_Deckler's post here:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/td-p/547907

Thank you! Unfortunately this is not working for the date range of last 30 days from the date of selection.

AlexisOlson
Super User
Super User

DISTINCTCOUNT is not an additive measure by nature. It's only additive if there is no overlap between rows, otherwise, you're double counting some items.

 

I recommend reading this article for further detail:

https://www.sqlbi.com/articles/obtaining-accurate-totals-in-dax/

 

As a side note, I don't think your measures are working as you intend them to. In both, SELECTEDVALUE(dim_date[date]) is either the same as MAX(dim_date[date]) or is blank. In the second one, you are putting a variable inside CALCULATE. This doesn't modify the value at all since countof is a constant once defined as a variable, so you just get back countof regardless of what date filtering you're trying to do.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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