cancel
Showing results 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

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.

Any advise would be appreciated! Thank you

4 REPLIES 4
Helper I

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?

Super User

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

Helper I

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

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.

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.

Announcements

#### 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 Monthly Update - June 2024

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

#### 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.

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors