Deriving less granular table from dataset with high granularity

[tomrause]

Hi @all,

I want to set up a PBI-Desktop-Report, but when it comes to count POS or count special dates I face some difficulties.

I kindly ask for advice.

Situation as follows:

Dataset is from a MS-SQL-Server stored procedrue and contains Facts alreday aligned with Dimensions.

Facts are on high granularity with Customer (qpp. 450 P-o-S), SellingDate (aap. 2 Years) and Productcategory (20), which evaluates to app. 450 * 450 *20=4,05 Mio records.

One dimension for distinct(!) selling date is either '0' or '1' to identify this date as 'special event date', to mark whether on this date a special event at the PoS took place. within this granularity the number counts much to high....

Question:

How can I ...

derive a table from that dataset with less granularity inside PBI or

count directly the Number of PoS for PoS-Level for special days identifiable with '0' or '1'

I do not want to rework the SP or add new datasource unless no other solution possible.

Please help.

Thank you very much in advance

Tom

[v-sihou-msft · Accepted solution]

@tomrause

According to your description, you need to "aggregate" your dataset from your stored procedure to higher granularity. Right?

In this scenario, I think you can use SUMMARIZE() function to create a calculated table, you can filter the table expression with condition in FILTER() function and have your records group on specific columns. Please refer to articles below:

From SQL to DAX: Grouping Data
From SQL to DAX: Filtering Data

Regards,