Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jot
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
v-jianhe-msft
Resolver II
Resolver II

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
Resolver II
Resolver II

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

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

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:

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.