How to do "bitwise or aggregation" on a column? (or how to run aggregates on substrings?)
I would like to understand if and how I could perform a "bitwise or aggregation" on a column, to calculate the bitwise or for all values in a specific column, to be included in a DAX measure. Simplified dataset to explain The (simulated and drastically simplified) source data I have is daily data which consists of the following dimensional attributes date in the form of day of month / month customer category product_group and has many numerical metrics, in this example represented by value_1. In PowerBI I have many measures defined to which sum etc. on the value_xx columns which I can group / display across many of the dimensions. The problem Due to load peformance I want to change the PowerBI dashboard to monthly aggregates, vs. daily data which in 99% of the cases is looked at on a monthly basis only. I can do a lot of aggregations to monthly basis in the SQL load scripts, because the majority of my measures is summing anyhow. However one of the key measures I have is the distinct count of days per month, which nicely calculates in the PowerBI report across all the different dimensions, filters, etc. because I have the individual days as my finest granularity. When I move up to monthly granularity, I will lose the ability to run these distinct days per month counts. Prepare data to monthly aggregate in my source system To overcome this challenge I thought of generating a bit string of length 31 (representing day 1 - 31 in the month) as part of my datapreparations (simple pseudo formula: string(10^(day of month -1)) So: on day 1- bit 1 is set to 1 on day 2, bit 2 is set to 1 ... on day 31, bit 31 is set to 1 an example can be found in the 'day_of_month_bit_position' column in the sample data set below. since this is a string, I can also calculate the decimal reprenstation for this based on bin2dec conversions in my source system. that's what is found in day_of_month_bit_as_decimal. In my source I can also generate the bitwise or aggregates when grouping into my month aggregates, which practically means that for each position in the string the maximum value is taken across all observations I aggregate over. So the monthly aggregate will have 1s on the bits representing the days we had data for. Examle (with week bit string representing mo-sun, because 31 is a lot of 1s and zeroes in an example): aggregate 1, data on mon, wed and fri --> 1010100 (3 unique days) aggregate 2, data on wed, fri, sat and sun --> 0010111 (4 unique days) the bitwise or of this results in 1010111 (5 unique days, mon, wed, fri, sat, sun) How to do this same aggregation in PowerBI using a measure? I want to be able to do this same 'bitwise or aggregation' in PowerBI to count the unique days in my current context, to do so I thought of the following steps generate the 'bitwise or aggregated' bit string sum all 1s / bit_count the bit string return this value as the unique days Struggling to get it working I have been banging my head against the wall now for some hours to get anywhere with this. Constantly ending up in dead ends 😞 . To summarize (in the example below): when I aggregate month 2 for customer_1 the bitwise or aggregate should result in 0000100100000100000000000000000 summing over/bitcounting this bit string results in 3 unique days. Note: I know this is still daily data, I on purpose provided this example instead of the monthly_pre_aggregates which I can create from my source, because validation of the calculation is still possible, since day_of_month is present.