Solved: Slicer to choose records with no data in fact tabl...

PBIUser007 · ‎03-30-2021

Hi All,

I have a requirement to create a slicer based on Booking %. Given below are the tables used and the expected report output.

My report should show the dates without any bookings as well (checked show items with no data option for this).

Now I need to create a slicer which shows Booking% as 0% when no hours are booked in the fact table on a particular date. Since slicers does not accept measures, I need to crete a calculated column for booking% which should filter the dates which does not have any data in fact table on selecting 0% in the slicer. Does anyone have any idea on how to achieve this?

Tables Used:

Expected Report Output:

Thanks in Advance

v-yangliu-msft · ‎04-05-2021

Hi @PBIUser007 ,

Here are the steps you can follow：

Modify the data of the Date Table to look like this:

1. Click Transform data to enter power query, click merge queries as new, and leave Date Table and Resource Table as Left Outer.

Result：

2. Perform Left Outer on Merge1 Table and Bookings Table

Result：

3. Create calculated column.

Book% = 'Merge2'[BookingsTable.Hours Booked]/8

4. Select Book% in Merge2 as the slicer

5. Result：

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Syndicate_Admin · ‎04-06-2021

Don't @PBIUser007,

This is an optimized and newly written method

Here are the steps you can follow:

1. Create a calculated table,

Table =
GENERATEALL(
    'Resource Table','Date Table')

result:

2. Create a calculated table.

Table 2 =
GENERATEALL(
    'Table',
    var _tableid='Table'[Resource ID]
    var _tabledate='Table'[date]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('BookingsTable','BookingsTable'[Resource ID]=_tableid&&'BookingsTable'[Date]=_tabledate),
        "Hours Booked",'BookingsTable'[Hours Booked]))

3. Create a calculated column.

Book% = 'Table 2'[Hours Booked]/8

4. Use The Book% of Table2 as a cutter

5. Result.

You can download the PBIX file from here.

Best regards

Liu Yang

If this post helps, consider Accepting it as the solution to help other members find it faster.

View solution in original post

Syndicate_Admin · ‎04-06-2021

Don't @PBIUser007,

This is an optimized and newly written method

Here are the steps you can follow:

1. Create a calculated table,

Table =
GENERATEALL(
    'Resource Table','Date Table')

result:

2. Create a calculated table.

Table 2 =
GENERATEALL(
    'Table',
    var _tableid='Table'[Resource ID]
    var _tabledate='Table'[date]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('BookingsTable','BookingsTable'[Resource ID]=_tableid&&'BookingsTable'[Date]=_tabledate),
        "Hours Booked",'BookingsTable'[Hours Booked]))

3. Create a calculated column.

Book% = 'Table 2'[Hours Booked]/8

4. Use The Book% of Table2 as a cutter

5. Result.

You can download the PBIX file from here.

Best regards

Liu Yang

If this post helps, consider Accepting it as the solution to help other members find it faster.

Syndicate_Admin · ‎04-06-2021

Don't @PBIUser007,

This is an optimized and newly written method

Here are the steps you can follow:

1. Create a calculated table,

Table =
GENERATEALL(
    'Resource Table','Date Table')

result:

2. Create a calculated table.

Table 2 =
GENERATEALL(
    'Table',
    var _tableid='Table'[Resource ID]
    var _tabledate='Table'[date]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('BookingsTable','BookingsTable'[Resource ID]=_tableid&&'BookingsTable'[Date]=_tabledate),
        "Hours Booked",'BookingsTable'[Hours Booked]))

3. Create a calculated column.

Book% = 'Table 2'[Hours Booked]/8

4. Use The Book% of Table2 as a cutter

5. Result.

You can download the PBIX file from here.

Best regards

Liu Yang

If this post helps, consider Accepting it as the solution to help other members find it faster.

Syndicate_Admin · ‎04-06-2021

Don't @PBIUser007,

This is an optimized and newly written method

Here are the steps you can follow:

1. Create a calculated table,

Table =
GENERATEALL(
    'Resource Table','Date Table')

result:

2. Create a calculated table.

Table 2 =
GENERATEALL(
    'Table',
    var _tableid='Table'[Resource ID]
    var _tabledate='Table'[date]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('BookingsTable','BookingsTable'[Resource ID]=_tableid&&'BookingsTable'[Date]=_tabledate),
        "Hours Booked",'BookingsTable'[Hours Booked]))

3. Create a calculated column.

Book% = 'Table 2'[Hours Booked]/8

4. Use The Book% of Table2 as a cutter

5. Result.

You can download the PBIX file from here.

Best regards

Liu Yang

If this post helps, consider Accepting it as the solution to help other members find it faster.

Syndicate_Admin · ‎04-06-2021

Don't @PBIUser007,

This is an optimized and newly written method

Here are the steps you can follow:

1. Create a calculated table,

Table =
GENERATEALL(
    'Resource Table','Date Table')

result:

2. Create a calculated table.

Table 2 =
GENERATEALL(
    'Table',
    var _tableid='Table'[Resource ID]
    var _tabledate='Table'[date]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('BookingsTable','BookingsTable'[Resource ID]=_tableid&&'BookingsTable'[Date]=_tabledate),
        "Hours Booked",'BookingsTable'[Hours Booked]))

3. Create a calculated column.

Book% = 'Table 2'[Hours Booked]/8

4. Use The Book% of Table2 as a cutter

5. Result.

You can download the PBIX file from here.

Best regards

Liu Yang

If this post helps, consider Accepting it as the solution to help other members find it faster.

Syndicate_Admin · ‎04-06-2021

Don't @PBIUser007,

This is an optimized and newly written method

Here are the steps you can follow:

1. Create a calculated table,

Table =
GENERATEALL(
    'Resource Table','Date Table')

result:

2. Create a calculated table.

Table 2 =
GENERATEALL(
    'Table',
    var _tableid='Table'[Resource ID]
    var _tabledate='Table'[date]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('BookingsTable','BookingsTable'[Resource ID]=_tableid&&'BookingsTable'[Date]=_tabledate),
        "Hours Booked",'BookingsTable'[Hours Booked]))

3. Create a calculated column.

Book% = 'Table 2'[Hours Booked]/8

4. Use The Book% of Table2 as a cutter

5. Result.

You can download the PBIX file from here.

Best regards

Liu Yang

If this post helps, consider Accepting it as the solution to help other members find it faster.

Syndicate_Admin · ‎04-06-2021

Don't @PBIUser007,

This is an optimized and newly written method

Here are the steps you can follow:

1. Create a calculated table,

Table =
GENERATEALL(
    'Resource Table','Date Table')

result:

2. Create a calculated table.

Table 2 =
GENERATEALL(
    'Table',
    var _tableid='Table'[Resource ID]
    var _tabledate='Table'[date]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('BookingsTable','BookingsTable'[Resource ID]=_tableid&&'BookingsTable'[Date]=_tabledate),
        "Hours Booked",'BookingsTable'[Hours Booked]))

3. Create a calculated column.

Book% = 'Table 2'[Hours Booked]/8

4. Use The Book% of Table2 as a cutter

5. Result.

You can download the PBIX file from here.

Best regards

Liu Yang

If this post helps, consider Accepting it as the solution to help other members find it faster.

Syndicate_Admin · ‎04-06-2021

Don't @PBIUser007,

This is an optimized and newly written method

Here are the steps you can follow:

1. Create a calculated table,

Table =
GENERATEALL(
    'Resource Table','Date Table')

result:

2. Create a calculated table.

Table 2 =
GENERATEALL(
    'Table',
    var _tableid='Table'[Resource ID]
    var _tabledate='Table'[date]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('BookingsTable','BookingsTable'[Resource ID]=_tableid&&'BookingsTable'[Date]=_tabledate),
        "Hours Booked",'BookingsTable'[Hours Booked]))

3. Create a calculated column.

Book% = 'Table 2'[Hours Booked]/8

4. Use The Book% of Table2 as a cutter

5. Result.

You can download the PBIX file from here.

Best regards

Liu Yang

If this post helps, consider Accepting it as the solution to help other members find it faster.

Syndicate_Admin · ‎04-06-2021

Don't @PBIUser007,

This is an optimized and newly written method

Here are the steps you can follow:

1. Create a calculated table,

Table =
GENERATEALL(
    'Resource Table','Date Table')

result:

2. Create a calculated table.

Table 2 =
GENERATEALL(
    'Table',
    var _tableid='Table'[Resource ID]
    var _tabledate='Table'[date]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('BookingsTable','BookingsTable'[Resource ID]=_tableid&&'BookingsTable'[Date]=_tabledate),
        "Hours Booked",'BookingsTable'[Hours Booked]))

3. Create a calculated column.

Book% = 'Table 2'[Hours Booked]/8

4. Use The Book% of Table2 as a cutter

5. Result.

You can download the PBIX file from here.

Best regards

Liu Yang

If this post helps, consider Accepting it as the solution to help other members find it faster.

Syndicate_Admin · ‎04-06-2021

Don't @PBIUser007,

This is an optimized and newly written method

Here are the steps you can follow:

1. Create a calculated table,

Table =
GENERATEALL(
    'Resource Table','Date Table')

result:

2. Create a calculated table.

Table 2 =
GENERATEALL(
    'Table',
    var _tableid='Table'[Resource ID]
    var _tabledate='Table'[date]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('BookingsTable','BookingsTable'[Resource ID]=_tableid&&'BookingsTable'[Date]=_tabledate),
        "Hours Booked",'BookingsTable'[Hours Booked]))

3. Create a calculated column.

Book% = 'Table 2'[Hours Booked]/8

4. Use The Book% of Table2 as a cutter

5. Result.

You can download the PBIX file from here.

Best regards

Liu Yang

If this post helps, consider Accepting it as the solution to help other members find it faster.

Syndicate_Admin · ‎04-06-2021

Don't @PBIUser007,

This is an optimized and newly written method

Here are the steps you can follow:

1. Create a calculated table,

Table =
GENERATEALL(
    'Resource Table','Date Table')

result:

2. Create a calculated table.

Table 2 =
GENERATEALL(
    'Table',
    var _tableid='Table'[Resource ID]
    var _tabledate='Table'[date]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('BookingsTable','BookingsTable'[Resource ID]=_tableid&&'BookingsTable'[Date]=_tabledate),
        "Hours Booked",'BookingsTable'[Hours Booked]))

3. Create a calculated column.

Book% = 'Table 2'[Hours Booked]/8

4. Use The Book% of Table2 as a cutter

5. Result.

You can download the PBIX file from here.

Best regards

Liu Yang

If this post helps, consider Accepting it as the solution to help other members find it faster.

Syndicate_Admin · ‎04-06-2021

Don't @PBIUser007,

This is an optimized and newly written method

Here are the steps you can follow:

1. Create a calculated table,

Table =
GENERATEALL(
    'Resource Table','Date Table')

result:

2. Create a calculated table.

Table 2 =
GENERATEALL(
    'Table',
    var _tableid='Table'[Resource ID]
    var _tabledate='Table'[date]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('BookingsTable','BookingsTable'[Resource ID]=_tableid&&'BookingsTable'[Date]=_tabledate),
        "Hours Booked",'BookingsTable'[Hours Booked]))

3. Create a calculated column.

Book% = 'Table 2'[Hours Booked]/8

4. Use The Book% of Table2 as a cutter

5. Result.

You can download the PBIX file from here.

Best regards

Liu Yang

If this post helps, consider Accepting it as the solution to help other members find it faster.

Syndicate_Admin · ‎04-06-2021

Don't @PBIUser007,

This is an optimized and newly written method

Here are the steps you can follow:

1. Create a calculated table,

Table =
GENERATEALL(
    'Resource Table','Date Table')

result:

2. Create a calculated table.

Table 2 =
GENERATEALL(
    'Table',
    var _tableid='Table'[Resource ID]
    var _tabledate='Table'[date]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('BookingsTable','BookingsTable'[Resource ID]=_tableid&&'BookingsTable'[Date]=_tabledate),
        "Hours Booked",'BookingsTable'[Hours Booked]))

3. Create a calculated column.

Book% = 'Table 2'[Hours Booked]/8

4. Use The Book% of Table2 as a cutter

5. Result.

You can download the PBIX file from here.

Best regards

Liu Yang

If this post helps, consider Accepting it as the solution to help other members find it faster.

Syndicate_Admin · ‎04-06-2021

Don't @PBIUser007,

This is an optimized and newly written method

Here are the steps you can follow:

1. Create a calculated table,

Table =
GENERATEALL(
    'Resource Table','Date Table')

result:

2. Create a calculated table.

Table 2 =
GENERATEALL(
    'Table',
    var _tableid='Table'[Resource ID]
    var _tabledate='Table'[date]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('BookingsTable','BookingsTable'[Resource ID]=_tableid&&'BookingsTable'[Date]=_tabledate),
        "Hours Booked",'BookingsTable'[Hours Booked]))

3. Create a calculated column.

Book% = 'Table 2'[Hours Booked]/8

4. Use The Book% of Table2 as a cutter

5. Result.

You can download the PBIX file from here.

Best regards

Liu Yang

If this post helps, consider Accepting it as the solution to help other members find it faster.

Syndicate_Admin · ‎04-06-2021

Don't @PBIUser007,

This is an optimized and newly written method

Here are the steps you can follow:

1. Create a calculated table,

Table =
GENERATEALL(
    'Resource Table','Date Table')

result:

2. Create a calculated table.

Table 2 =
GENERATEALL(
    'Table',
    var _tableid='Table'[Resource ID]
    var _tabledate='Table'[date]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('BookingsTable','BookingsTable'[Resource ID]=_tableid&&'BookingsTable'[Date]=_tabledate),
        "Hours Booked",'BookingsTable'[Hours Booked]))

3. Create a calculated column.

Book% = 'Table 2'[Hours Booked]/8

4. Use The Book% of Table2 as a cutter

5. Result.

You can download the PBIX file from here.

Best regards

Liu Yang

If this post helps, consider Accepting it as the solution to help other members find it faster.

Syndicate_Admin · ‎04-06-2021

Don't @PBIUser007,

This is an optimized and newly written method

Here are the steps you can follow:

1. Create a calculated table,

Table =
GENERATEALL(
    'Resource Table','Date Table')

result:

2. Create a calculated table.

Table 2 =
GENERATEALL(
    'Table',
    var _tableid='Table'[Resource ID]
    var _tabledate='Table'[date]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('BookingsTable','BookingsTable'[Resource ID]=_tableid&&'BookingsTable'[Date]=_tabledate),
        "Hours Booked",'BookingsTable'[Hours Booked]))

3. Create a calculated column.

Book% = 'Table 2'[Hours Booked]/8

4. Use The Book% of Table2 as a cutter

5. Result.

You can download the PBIX file from here.

Best regards

Liu Yang

If this post helps, consider Accepting it as the solution to help other members find it faster.

Syndicate_Admin · ‎04-06-2021

Don't @PBIUser007,

This is an optimized and newly written method

Here are the steps you can follow:

1. Create a calculated table,

Table =
GENERATEALL(
    'Resource Table','Date Table')

result:

2. Create a calculated table.

Table 2 =
GENERATEALL(
    'Table',
    var _tableid='Table'[Resource ID]
    var _tabledate='Table'[date]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('BookingsTable','BookingsTable'[Resource ID]=_tableid&&'BookingsTable'[Date]=_tabledate),
        "Hours Booked",'BookingsTable'[Hours Booked]))

3. Create a calculated column.

Book% = 'Table 2'[Hours Booked]/8

4. Use The Book% of Table2 as a cutter

5. Result.

You can download the PBIX file from here.

Best regards

Liu Yang

If this post helps, consider Accepting it as the solution to help other members find it faster.

Syndicate_Admin · ‎04-06-2021

Don't @PBIUser007,

This is an optimized and newly written method

Here are the steps you can follow:

1. Create a calculated table,

Table =
GENERATEALL(
    'Resource Table','Date Table')

result:

2. Create a calculated table.

Table 2 =
GENERATEALL(
    'Table',
    var _tableid='Table'[Resource ID]
    var _tabledate='Table'[date]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('BookingsTable','BookingsTable'[Resource ID]=_tableid&&'BookingsTable'[Date]=_tabledate),
        "Hours Booked",'BookingsTable'[Hours Booked]))

3. Create a calculated column.

Book% = 'Table 2'[Hours Booked]/8

4. Use The Book% of Table2 as a cutter

5. Result.

You can download the PBIX file from here.

Best regards

Liu Yang

If this post helps, consider Accepting it as the solution to help other members find it faster.

Syndicate_Admin · ‎04-06-2021

Don't @PBIUser007,

This is an optimized and newly written method

Here are the steps you can follow:

1. Create a calculated table,

Table =
GENERATEALL(
    'Resource Table','Date Table')

result:

2. Create a calculated table.

Table 2 =
GENERATEALL(
    'Table',
    var _tableid='Table'[Resource ID]
    var _tabledate='Table'[date]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('BookingsTable','BookingsTable'[Resource ID]=_tableid&&'BookingsTable'[Date]=_tabledate),
        "Hours Booked",'BookingsTable'[Hours Booked]))

3. Create a calculated column.

Book% = 'Table 2'[Hours Booked]/8

4. Use The Book% of Table2 as a cutter

5. Result.

You can download the PBIX file from here.

Best regards

Liu Yang

If this post helps, consider Accepting it as the solution to help other members find it faster.

Syndicate_Admin · ‎04-06-2021

Don't @PBIUser007,

This is an optimized and newly written method

Here are the steps you can follow:

1. Create a calculated table,

Table =
GENERATEALL(
    'Resource Table','Date Table')

result:

2. Create a calculated table.

Table 2 =
GENERATEALL(
    'Table',
    var _tableid='Table'[Resource ID]
    var _tabledate='Table'[date]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('BookingsTable','BookingsTable'[Resource ID]=_tableid&&'BookingsTable'[Date]=_tabledate),
        "Hours Booked",'BookingsTable'[Hours Booked]))

3. Create a calculated column.

Book% = 'Table 2'[Hours Booked]/8

4. Use The Book% of Table2 as a cutter

5. Result.

You can download the PBIX file from here.

Best regards

Liu Yang

If this post helps, consider Accepting it as the solution to help other members find it faster.

Syndicate_Admin · ‎04-06-2021

Don't @PBIUser007,

This is an optimized and newly written method

Here are the steps you can follow:

1. Create a calculated table,

Table =
GENERATEALL(
    'Resource Table','Date Table')

result:

2. Create a calculated table.

Table 2 =
GENERATEALL(
    'Table',
    var _tableid='Table'[Resource ID]
    var _tabledate='Table'[date]
    return
    SELECTCOLUMNS(
        CALCULATETABLE('BookingsTable','BookingsTable'[Resource ID]=_tableid&&'BookingsTable'[Date]=_tabledate),
        "Hours Booked",'BookingsTable'[Hours Booked]))

3. Create a calculated column.

Book% = 'Table 2'[Hours Booked]/8

4. Use The Book% of Table2 as a cutter

5. Result.

You can download the PBIX file from here.

Best regards

Liu Yang

If this post helps, consider Accepting it as the solution to help other members find it faster.

Slicer to choose records with no data in fact table

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly