Forum Discussion
Apply date slicer to GROUPBY table
I have a table with multiple records per customer key (pi_entity_fk), with an "open" and "click" flag which I want to aggregate it by and then allow the user to filter it on different date ranges - so dynamic aggregations, based on a changing date range.
I have this DAX query which does the aggregation I want, but on all of the data
NOTE: [entity_key] is just pi_entity_fk formatted as text so it behaves as a categorical variable and not a measure to be aggregated
Summary_Table =
GROUPBY('cmi SK_powerbi_summarize_test', 'cmi SK_powerbi_summarize_test'[entity_key], "Count_Has_Opened", SUMX( CURRENTGROUP (), 'cmi SK_powerbi_summarize_test'[has_opened]), "Count_Has_Clicked", SUMX( CURRENTGROUP (), 'cmi SK_powerbi_summarize_test'[has_clicked]))
I just don't know how to add the filter element ... or am I approaching it fromm the wrong direction. Sample data below.
OK after a lot of banging my head against the desk, and heading off in a different direction, I came up with a solution - hold on to your hats it's tricky
1) Create a summary table of the customer key (pi_entity_fk)
entity_summary = GROUPBY('SK_powerbi_summarize_test', 'SK_powerbi_summarize_test'[pi_entity_fk],
"Count_Records", COUNTX( CURRENTGROUP (),'SK_powerbi_summarize_test'[has_opened]))2) Creat a relationship between the entity_summary table and the orginal table it was create from SK_powerbi_summarize_test joining on the customer key pi_entity_fk
3) Create 2 measures - one to count the values and the other to band them to use in charts. I expected it to stop here and then use these measure in a chart, but you can't do that so we have a couple more steps
dynamic_has_opened = CALCULATE(sum(SK_powerbi_summarize_test[has_opened]))
has_opend_band2 = if([dynamic_has_opened] > 50, "Over 50", if([dynamic_has_opened] > 20, "21-50", if( [dynamic_has_opened] > 10, "11-20", if( [dynamic_has_opened] > 0, "1-10", "None"))))
4) Create a table containing the band descriptions in the measure,
open_band_summary =
DATATABLE (
"open_band", STRING,
{
{"Over 50"},
{"21-50"},
{"11-20"},
{"1-10"},
{"None"}
}
)5) In this new summary table create a measure to count the occurances of each banded value in then entity_summary table
open_count =
VAR MeasureValue = SELECTEDVALUE('open_band_summary'[open_band])
RETURN COUNTX(FILTER('entity_summary', [has_opend_band2] = MeasureValue), [has_opend_band2])Now you can chart the summarised data and a date slider linked to the orignal data table SK_powerbi_summarize_test date field will update the chart
3 Replies
v-chuncz-msftCommunity Support
Values in a calculated table are fixed. You may drag fields and add measures to a Table visual.
So if tables are fixed there isn't a way to do a dynamic table and then point charts at that dynamic table.
Like
select entit_key, sum(has_opened)
from inimported_table
where send_data between dateA and dateB
group by entity_key
and have dateA and dateB controlled by a slider
OK after a lot of banging my head against the desk, and heading off in a different direction, I came up with a solution - hold on to your hats it's tricky
1) Create a summary table of the customer key (pi_entity_fk)
entity_summary = GROUPBY('SK_powerbi_summarize_test', 'SK_powerbi_summarize_test'[pi_entity_fk],
"Count_Records", COUNTX( CURRENTGROUP (),'SK_powerbi_summarize_test'[has_opened]))2) Creat a relationship between the entity_summary table and the orginal table it was create from SK_powerbi_summarize_test joining on the customer key pi_entity_fk
3) Create 2 measures - one to count the values and the other to band them to use in charts. I expected it to stop here and then use these measure in a chart, but you can't do that so we have a couple more steps
dynamic_has_opened = CALCULATE(sum(SK_powerbi_summarize_test[has_opened]))
has_opend_band2 = if([dynamic_has_opened] > 50, "Over 50", if([dynamic_has_opened] > 20, "21-50", if( [dynamic_has_opened] > 10, "11-20", if( [dynamic_has_opened] > 0, "1-10", "None"))))
4) Create a table containing the band descriptions in the measure,
open_band_summary =
DATATABLE (
"open_band", STRING,
{
{"Over 50"},
{"21-50"},
{"11-20"},
{"1-10"},
{"None"}
}
)5) In this new summary table create a measure to count the occurances of each banded value in then entity_summary table
open_count =
VAR MeasureValue = SELECTEDVALUE('open_band_summary'[open_band])
RETURN COUNTX(FILTER('entity_summary', [has_opend_band2] = MeasureValue), [has_opend_band2])Now you can chart the summarised data and a date slider linked to the orignal data table SK_powerbi_summarize_test date field will update the chart