I have created a CALCULATED COLUMN for bucketing impressions:
Impression Bucket =
VAR TotalImpressions = [Total Impressions new df Final]
RETURN
SWITCH(
TRUE(),
TotalImpressions >= 1 && TotalImpressions <= 5, "1 to 5",
TotalImpressions > 5 && TotalImpressions <= 10, "6 to 10",
TotalImpressions > 10 && TotalImpressions <= 15, "11 to 15",
TotalImpressions > 15 && TotalImpressions <= 20, "16 to 20",
TotalImpressions > 20 && TotalImpressions <= 50, "21 to 50",
TotalImpressions > 50 && TotalImpressions <= 100, "51 to 100",
TotalImpressions > 100, "100+"
/* "Other" */ // Catch-all category for zero or null impressions
)
The below impressions formula is a MEASURE being referenced in the above formula
Total Impressions new df Final =
CALCULATE(
SUM('impression frequency dataflow'[activity_count]),
'impression frequency dataflow'[activity_type] = "IMPRESSION",
ALLEXCEPT(
'impression frequency dataflow',
'impression frequency dataflow'[date],
'impression frequency dataflow'[brand_name],
'impression frequency dataflow'[h_id]
)
)
Visual added in power bi:
In a bar chart :
x axis: Impression Bucket
y axis: distinct count of h_id (h_id is a column containing id's , just taking distinct count of it)
global filters : brand_name, date (these are the same fields used in the above formulas)
Note: date field is in month granularity (for ex: jan, feb, mar: when multiple months are selected )
getting results only :
when i am selecting a single brand from brand_name filter i am able to get correct result in visual
when i am selecting a single date from date filter i am able to get correct result in visual
when single brand and single date is selected i am able to get correct result in visual
Require solution for:
when i select multiple brands from brand_name filter, then i am not able to get correct result
when i select multiple dates from date filter, then i am not able to get correct result
i am getting very high inflated results when multiple brands or dates are selected (basically getting dist count more than total h_id) but when measured in sql i am getting correct results, not sure what granularity is causing the issue.
