Forum Discussion
Deriving less granular table from dataset with high granularity
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
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 DataRegards,
1 Reply
v-sihou-msftMicrosoft Employee
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 DataRegards,