Excel data model countif equivalent

Question

I new to using data models with Excel. I have been successfully extracting specific values from the data model for a report using the cubemember function. What I am have trouble with is finding cube function equivalent for the normal excel function countif. I would like to count the occurrence of a specific value within a column of one the tables in my data model.

Here would be a very simplified example of one of the data model tables

And below is how I would like the excel report to look like,

So within a cube function I would like get a count of the occurrences of 'A'.

The next functionality I would like to achieve is then to grab all of the corresponding serial numbers for 'A' and then output them into a single excel cell.

I have tried the following (where the cell reference $H$39 is the specific 'Type' being considered, in this case 'A'),

=CUBESETCOUNT(CUBESET("ThisWorkbookDataModel","[Table A].[Type].["&$H$39&"]")) But this simply returns a value of 1, rather than 4.

I am not sure even you to start the second query for grabbing the serial numbers.

question from:https://stackoverflow.com/questions/65623224/excel-data-model-countif-equivalent

Answer 1

Waitting for answers