User Profile
Valentb
Advocate I
Joined 8 years ago
User Widgets
Contributions
DistinctCount - aggregation - not working - I'm lost
Hi there! I've been struggling with distinctcount per transaction_id when trying to use an agg.table. It's been more than 7 months since this issue came to light, but I cannot see a solution, and I feel pretty clueless where i'm messing it up. It works fine when in Directquery mode as long as I don't use an agg. table, but the results take up to 30~100 seconds to complete and is not a viable solution long term. I set ut the sql table as an agg as well, so have access to both Powerbi, and SQL server. The transaction_id is unique to each complete transaction, and we have around 50 million per year (it's around 16-17 digits long) The fact table consists of transactions rows with a 1-10 (lines or products) per transaction_id Agg table sql side: ... agg_final as ( select store_number, product_id, cal_date, count(distinct(transaction_id)) as transaction_id sum(sales_amount) as sales_amount 1 as fusion_key count (*) as count_rows from fact_sales Group by store_number, product_id, cal_date ) The dax i'm using is a simple calculate(distinctcount(transaction_id),TREATAS{1}, Fact_table[Fusion_key])) The agg does get a match in dax studio, but the results are pretty wrong when using the agg table, Works perfect when I don't use agg. table. Also using the product_name in a matrix. The fusionkey does not change anything in the dax measure - seem irrelevant as GuyInaCube video tried to explain. So it's either a sql side error or powerbi, unsure where to even debug. Thanks for your time.Compare same campaign - Different dates in Previous Year Using DAX
Hi Guys, I'm trying to solve a puzzle. I have a date_dim that contain dates and campaign name. Since the campaigns both can have variable dates but needs to be the same 'campaign name -1 year' I cannot use sameperiod last year, but maybe date add and some variable? I'm just really stuck and have no clue how to solve this. Thanks alot for any help
