Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, I'm creating a report which comprises a parent table with a number of child tables which have a [begin_date] and [end_date]. The report (all its visuals) is supposed to show only the child table values for a specific date.
When using page level filters, I would need to enter a [begin_date] >= 'date' and [end_date] <= 'date' for each child table.
When using slicers, I can only slice either on [begin_date] or [end_date]. Again for each child table.
Both options are too cumbersome since they mean having to enter the same date many times.
Ideally, I would have a prompt on the page, in which a specific date can be entered. What approach should I choose here?
Many thanks in advance,
Solved! Go to Solution.
Hi,
So you wish to make the bunch of child tables get filtered at one time, controlled by a slicer or a parameter, and what you do not want is to set the filter one by one for the child tables, right?
If so, I think the point is not a slicer or parameter or page filter, but the relationships of the tables. Because there are not relationships among these tables, they will not get filter at one time. So it is important to establish a “common table” to connect to these child tables. In my case, I create two common tables, one for begin_date and the other for end_date.
Below are my steps:
begindate table = DISTINCT(UNION(SELECTCOLUMNS(child1,"begin_date",child1[begin_date]),SELECTCOLUMNS(child2,"begin_date",child2[begin_date]),SELECTCOLUMNS(child3,"begin_date",child3[begin_date])))
end_date table = DISTINCT(UNION(SELECTCOLUMNS(child1,"end_date",child1[end_date]),SELECTCOLUMNS(child2,"end_date",child2[end_date]),SELECTCOLUMNS(child3,"end_date",child3[end_date])))
4. Add two slicers for begin_date and end_date, when filtering, all three child tables can be filtered at one time.
5. If you want to use parameter, you could add parameter on the begin_date table and end_date table, then three child tables will get filtered as well.
Best Regards,
Henry
Hi,
So you wish to make the bunch of child tables get filtered at one time, controlled by a slicer or a parameter, and what you do not want is to set the filter one by one for the child tables, right?
If so, I think the point is not a slicer or parameter or page filter, but the relationships of the tables. Because there are not relationships among these tables, they will not get filter at one time. So it is important to establish a “common table” to connect to these child tables. In my case, I create two common tables, one for begin_date and the other for end_date.
Below are my steps:
begindate table = DISTINCT(UNION(SELECTCOLUMNS(child1,"begin_date",child1[begin_date]),SELECTCOLUMNS(child2,"begin_date",child2[begin_date]),SELECTCOLUMNS(child3,"begin_date",child3[begin_date])))
end_date table = DISTINCT(UNION(SELECTCOLUMNS(child1,"end_date",child1[end_date]),SELECTCOLUMNS(child2,"end_date",child2[end_date]),SELECTCOLUMNS(child3,"end_date",child3[end_date])))
4. Add two slicers for begin_date and end_date, when filtering, all three child tables can be filtered at one time.
5. If you want to use parameter, you could add parameter on the begin_date table and end_date table, then three child tables will get filtered as well.
Best Regards,
Henry
Dear Henry,
Thank you very much for your elaborate and swift response; I would never have thought of that...
I'm facing some issues when implementing your solution.
The new begin/end date tables contain a blank value. Therefore I cannot create the relationships.
I tried to solve this by creating a second set of begin/end date tables:
BEGINDATETABLE2 = filter(BEGINDATETABLE,BEGINDATETABLE[BEG_DATE])
The BEG_DATE isn't recognised however:
I checked the formatting of all tables; they fields are all formatted as *14/03/2001 13:30:5 (G).
Probably the FILTER is messing up the join.
Probably I only need to know how to omit the blank value from the original table - any ideas are very welcome!
All the best, Jot
Hi,
Thank you for your reply. What about using ALLNOBLANKROWS function?
for example:
begin_date 2 = ALLNOBLANKROW('begindate table')
Best Regards,
Henry
I tried this too, but the blank row somehow remained.
I created the begindatetable as you suggested:
When using the ALLNOBLANKS() function, the blank row remains:
The FILTER() does remove the blank row. I also tried the VALUES() function but this didn't erase the blank row either.
Hi,
Thank you for your reply. So what about remove the blank value from the original datasource, then imort it the Power BI Desktop?
Best Regards,
Henry
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |