cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jot Frequent Visitor
Frequent Visitor

Display only value on given day

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,

1 ACCEPTED SOLUTION

Accepted Solutions
v-jianhe-msft Regular Visitor
Regular Visitor

Re: 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:

  1. The parent table together with three child tables I use:1.PNG
    1. After loading them to power bi, create two more new tables using DAX:
  2.  

      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])))

     2.PNG

  3. 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. 

9.PNG

10.PNG

 

 

4.  Add two slicers for begin_date and end_date, when filtering, all three child tables can be filtered at one time.

11.PNG

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

View solution in original post

5 REPLIES 5
v-jianhe-msft Regular Visitor
Regular Visitor

Re: 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:

  1. The parent table together with three child tables I use:1.PNG
    1. After loading them to power bi, create two more new tables using DAX:
  2.  

      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])))

     2.PNG

  3. 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. 

9.PNG

10.PNG

 

 

4.  Add two slicers for begin_date and end_date, when filtering, all three child tables can be filtered at one time.

11.PNG

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

View solution in original post

Jot Frequent Visitor
Frequent Visitor

Re: Display only value on given day

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:

pbi_1.JPG

 

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

v-jianhe-msft Regular Visitor
Regular Visitor

Re: Display only value on given day

Hi,

 

Thank you for your reply. What about using ALLNOBLANKROWS function? 

 

for example: 

 

begin_date 2 = ALLNOBLANKROW('begindate table')

 

Best Regards,

Henry 

 

Jot Frequent Visitor
Frequent Visitor

Re: Display only value on given day

I tried this too, but the blank row somehow remained.

 

I created the begindatetable as you suggested:

pbi_4.JPG

 

When using the ALLNOBLANKS() function, the blank row remains:

pbi_3.JPG

 

The FILTER() does remove the blank row. I also tried the VALUES() function but this didn't erase the blank row either.

v-jianhe-msft Regular Visitor
Regular Visitor

Re: Display only value on given day

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 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,401)