Forum Discussion
Display only value on given day
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:
- The parent table together with three child tables I use:
- After loading them to power bi, create two more new tables using DAX:
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])))
- Manage relationships for tables, connect begin_date table with three child tables, similarly, connecting end_date table with three child tables. Noted it is important to use "single " cross filter for all the relationships when building the relationships.
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
- The parent table together with three child tables I use:
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:
- The parent table together with three child tables I use:
- After loading them to power bi, create two more new tables using DAX:
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])))
- Manage relationships for tables, connect begin_date table with three child tables, similarly, connecting end_date table with three child tables. Noted it is important to use "single " cross filter for all the relationships when building the relationships.
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.
