DAX caluculate sum of children sums

Question

I am new in DAX, I used MDX before. I have following formula in MDX

    CREATE 
  MEMBER CURRENTCUBE.[Measures].[Count Visit] AS [Measures].[Unique Contact Count]
  ;    

scope([Date Cheque].[YEAR-MONTH-DATE].[Month Of Year], [Measures].[Count Visit]);
this= Sum
        ({[Date Cheque].[YEAR-MONTH-DATE].CurrentMember.Children}
         ,[Measures].[Count Visit]);
end scope;

scope([Date Cheque].[YEAR-MONTH-DATE].[Calendar Year], [Measures].[Count Visit]);
this= Sum
        ({[Date Cheque].[YEAR-MONTH-DATE].CurrentMember.Children}
         ,[Measures].[Count Visit]);
end scope;

I understand that there is no hierarchies in DAX, so I have no idea how to do thing like this

It would be much easier to help you if instead of MDX you describe your data model and a desired outcome. Data sample will be helpful too. — RADO

Dan Dan · Accepted Answer · 2019-02-20T15:02:46

In general, you can use filter inspection functions in DAX, such as ISFILTERED, ISCROSSFILTERED, HASONEVALUE and ISINSCOPE, to determine what the current level of calculation is. This allows us to do complex things that we would normally use the SCOPE statement for in MDX.

However, in this case, it seems that all you want to do, is to always evaluate [Measures].[Unique Contact Count] at the day-level, and then roll that up to higher levels. For this, we can simply write the [Count Visit] measure as follows, assuming your 'Date Cheque' table holds a [Date] column at the day grain:

SUMX(VALUES('Date Cheque'[Date]), [Unique Contact Count])

SUMX is a so-called iterator function, which will evaluate the 2nd argument for every row in the 1st argument (which must be a table expression), and return the sum of the 2nd argument.

DAX caluculate sum of children sums

1 Answers