Forum Discussion
Use slicer to filter countrows-table
I have these 3 tables. FACT_VALUES contains several items that belongs to a unit with their respective start and stop date. DIM_DATE contains all dates and how many units that are running on each day. DIM_UNIT contains all unique units and there is a relationship between the UNIT columns in DIM_UNIT and FACT_VALUES.
FACT_VALUES =
DATATABLE (
"UNIT"; STRING;
"START"; DATETIME;
"STOP"; DATETIME;
{
{ "A"; "2017-01-01"; "2017-01-03"};
{ "B"; "2017-01-05"; "2017-01-07"};
{ "B"; "2017-01-01"; "2017-01-06"};
{ "C"; "2017-01-02"; "2017-01-03"}
}
DIM_UNITS = SUMMARIZE('FACT_VALUES';'FACT_VALUES'[UNIT])
DIM_DATE = CALENDAR("2017-01-01";"2017-01-07")
// add column to DIM_DATE
UNITS_RUNNING = CALCULATE(
COUNTROWS('FACT_VALUES');
FILTER('FACT_VALUES';'FACT_VALUES'[START]<='DIM_DATE'[Date]);
FILTER('FACT_VALUES';'FACT_VALUES'[STOP]>='DIM_DATE'[Date]))
UNIT START STOP
| A | 2017-01-01 00:00:00 | 2017-01-03 00:00:00 |
| B | 2017-01-05 00:00:00 | 2017-01-07 00:00:00 |
| B | 2017-01-01 00:00:00 | 2017-01-06 00:00:00 |
| C | 2017-01-02 00:00:00 | 2017-01-03 00:00:00 |
UNIT
| A |
| B |
| C |
Date UNITS_RUNNING
| 2017-01-01 00:00:00 | 2 |
| 2017-01-02 00:00:00 | 3 |
| 2017-01-03 00:00:00 | 3 |
| 2017-01-04 00:00:00 | 1 |
| 2017-01-05 00:00:00 | 2 |
| 2017-01-06 00:00:00 | 2 |
| 2017-01-07 00:00:00 | 1 |
In this chart Date is used as axis and the value UNITS_RUNNING.
What I want to achieve is being able to filter UNITS_RUNNING with a slicer, using the values from the DIM_UNIT-table. How can I do this? I assume my UNITS_RUNNING formula needs to be rebuild, but I can't figure out how.
My real example has a couple of hundred different units and the date is one-hour intervals rather than dates, while the fact-values contains >20k rows. Also, my real unit table is has other relations and will be used to filter other charts on the page, so getting the unit into the datetable and use that in a slicer is not an option.
Thanks in advance!
Hi karlanka,
Based on your description, you want to use a slicer based on DIM_UNITS[UNIT] column to filter calculate column [UNITS_RUNNING] in DIM_DATE table and values in column [UNITS_RUNNING] can be changed by selectedvalue in slicer, right?
So your requirement equals to create a dynamic column, however, it's not supported in power bi. Please read the community knowledge base here: https://community.powerbi.com/t5/Community-Knowledge-Base/Dynamic-column-based-on-slicer-selection/ta-p/162635.
I have test on your solution, it works well but it's not recommended because the iteration times is very big.
As a workaround, please refer to steps below:
1. Create a relationship between FACT_VALUES table and DIM_UNITS table based on column [UNIT] like below:
2. Use measure instead of calculate column using DAX formula like this:
UNITS_RUNNING = CALCULATE( COUNTROWS('FACT_VALUES'), FILTER('FACT_VALUES', 'FACT_VALUES'[START]<=MAX('DIM_DATE'[Date])), FILTER('FACT_VALUES','FACT_VALUES'[STOP]>=MAX('DIM_DATE'[Date])), FILTER('FACT_VALUES', FACT_VALUES[UNIT] = RELATED(DIM_UNITS[UNIT])))The result is as below:
PBIX file: https://www.dropbox.com/s/dcsvyz6tcihnqvm/Use%20slicer%20to%20filter%20countrows-table.pbix?dl=0
Regards,
Jimmy Tao
3 Replies
One option I am thought of is creating another table as
running_per_date_table = CROSSJOIN(
CALENDAR("2017-01-01";"2017-01-07");
SUMMARIZE('FACT_VALUES'[UNIT]))And then add a column like:
UNITS_RUNNING = CALCULATE( COUNTROWS('FACT_VALUES'); FILTER('FACT_VALUES';'FACT_VALUES'[START]<='running_per_date_table'[Date]); FILTER('FACT_VALUES';'FACT_VALUES'[STOP]>='running_per_date_table'[Date]); FILTER('FACT_VALUES';'FACT_VALUES'[UNIT]='running_per_date_table'[UNIT]))But given this table will contain each hour of the last three years it seems like a bad idea to multiply its size by the number of unique units.
Hi karlanka,
Based on your description, you want to use a slicer based on DIM_UNITS[UNIT] column to filter calculate column [UNITS_RUNNING] in DIM_DATE table and values in column [UNITS_RUNNING] can be changed by selectedvalue in slicer, right?
So your requirement equals to create a dynamic column, however, it's not supported in power bi. Please read the community knowledge base here: https://community.powerbi.com/t5/Community-Knowledge-Base/Dynamic-column-based-on-slicer-selection/ta-p/162635.
I have test on your solution, it works well but it's not recommended because the iteration times is very big.
As a workaround, please refer to steps below:
1. Create a relationship between FACT_VALUES table and DIM_UNITS table based on column [UNIT] like below:
2. Use measure instead of calculate column using DAX formula like this:
UNITS_RUNNING = CALCULATE( COUNTROWS('FACT_VALUES'), FILTER('FACT_VALUES', 'FACT_VALUES'[START]<=MAX('DIM_DATE'[Date])), FILTER('FACT_VALUES','FACT_VALUES'[STOP]>=MAX('DIM_DATE'[Date])), FILTER('FACT_VALUES', FACT_VALUES[UNIT] = RELATED(DIM_UNITS[UNIT])))The result is as below:
PBIX file: https://www.dropbox.com/s/dcsvyz6tcihnqvm/Use%20slicer%20to%20filter%20countrows-table.pbix?dl=0
Regards,
Jimmy Tao