Hello,
I am having a problem with the total of a column that contains a measure. I do a distinct count in sales code to see how many sales we had in each month. I sum the individual rows (Months) but they have a different total than the shown in the visual. I have tried in many ways but I still have differences. In other columns, the sum of the individual totals matches the total from the visual, so it only happens in the sales code column.
For exmple, when adding the 5 values I get a total of 82.210 and not 81.705.
I attach my example so you can help me figure out this difference. : Distinct Count Total error
I would appreciate your help.
Best,
JALV
Hi @Andvil ,
As for the difference of the formulas:
1) The SUMMARIZE Formula:
CODE count =
SUMX(
SUMMARIZE(
'2020',
'Dates Table'[MonthNameLong]
),
CALCULATE(
DISTINCTCOUNT( '2020'[Code] )
)
)
You should never use this type of formula. The reason is SUMMARIZE function is buggy when it comes to adding columns. You can see here that the Name and Expression part of the function is not recommended and has been depreciated. Instead, you can use SUMMARIZE to group the columns, but use ADDCOLUMNS around it to add the columns, so the right way to use it is as follows:
Summarize Measure =
VAR Result =
SUMX(
ADDCOLUMNS(
SUMMARIZE(
'2020',
'Dates Table'[MonthNameLong]
),
"Record Count", CALCULATE(
DISTINCTCOUNT( '2020'[NUMERO DE CARGA FINAL] )
)
),
[Record Count]
)
RETURN
Result
You could do it without the VAR/RETURN construct. I always have that when building in DAX Studio. You can read more about properly using ADDCOLUMNS with SUMMARIZE here. You almost always have to wrap your expression for the ADDCOLUMNS function in CALCULATE, or refer to another measure, which puts in implicit CALCULATE around it, which causes a context transition. I try to avoid this as it can be expensive in terms of performance, but often it is the only or best solution. I personally am not a fan of ADDCOLUMNS/SUMMARIZE just because the DAX code is hideous to look at. 😁 Even DaxFormatter.com doesn't do a good job of formatting the code for me.
NOTE: There is a newer function called SUMMARIZECOLUMNS() but it should only be used for DAX queries or Calculated Tables, not measures. It doesn't handle context transitions at all. It won't return the wrong result - it will actually throw up a dialog box with an error that you cannot use it in the visual. You can use it for cards, but not much else. It is what I used though in DAX Studio to find out you had the same order number in multiple months. Great for queries. Not for measures.
2) My IF statement with the HASVALUES() measure is this:
If HasValues =
IF(
HASONEVALUE( '2020'[MES] ),
DISTINCTCOUNT( '2020'[NUMERO DE CARGA FINAL] ),
COUNTROWS(
ALLSELECTED(
'2020'[MES],
'2020'[NUMERO DE CARGA FINAL]
)
)
)
It looks for how many values there are in the [MES] column. If there is more than one, it has to switch the method it uses, otherwise it uses a simple DistinctCount. I also generall y try to avoid the IF() function, but it does come in handy when working with the HASONEVALUE() and related functions, like ISFILTERED, ISINSCOPE, etc.
3) The final SUMX measure I like the elgance of:
SumX Measure =
SUMX(
VALUES( 'Dates Table'[YearMonthnumber] ),
CALCULATE(
DISTINCTCOUNT( '2020'[NUMERO DE CARGA FINAL] )
)
)
It uses data lineage based on the YearMonth column of your date table, which allows for orders to be duplicated across months/years, then it does a DISTINCTCOUNT, but it only works with a CALCULATE around it to get down to the row context. Again, I try to avoid CALCULATE when I can, but it absolutely has its uses, and is used well in this measure.
4) I redid my HasValues with a HasValue 2 measure. This one would require you to change the column you use, and you should do this anyway. I used the MonthNameLong field, but you need to add a Month Number column. You should never use fields from your FACT table as a DIM field. You should always use your DIM table, or your date table for this. That is what they are for. I have many model shere I have hidden every single field in my FACT table and only use DIM tables and measures. I cannot always to this, but it is because I need to use some files only in my fact table, like Invoice numbers for a detailed report. Anyway, the updated measure is:
If HasValues 2 =
IF(
HASONEVALUE( 'Dates Table'[MonthNameLong] ),
DISTINCTCOUNT( '2020'[NUMERO DE CARGA FINAL] ),
COUNTROWS(
ALLSELECTED(
'2020'[MES],
'2020'[NUMERO DE CARGA FINAL]
)
)
)
So which is better? {shrug} I ran some performance tests. The DAX query times are negligable in all cases. The times below are in milliseconds, and rerunning the queries all return sub-100 millisecond times, which means they all work as fast as you can blink. So go with the one that you are most comfortable understanding! With performance times like this, if your report is slow, it has nothing to do with this particular measure, no matter which you pick.
One final thing: In your model, you need to turn off automatic date/time intelligence in options, then mark your date table as a date table by right-clicking on it and marking as a date table. Performance improvement is negligable (most DAX queries took 50%-75% of the time to run after that) but going from 20ms to 15ms isn't going to help you much. But it is good practice to have a proper date table. It also gets rid of the 8-9 hidden date tables Power BI creates behind the scenes when you have automatic date/time intelligence turned on.
And one absolutely final note: Please mark one or more of these as the solution so we can know this is resolved and others will know the solution that worked for you.
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
