Forum Discussion
Apply date slicer to GROUPBY table
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
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