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
remi
Advocate II
Advocate II

Summarize a table with filter and count, but keeping all the summarized category

Hello all,

 

Need some help!

 

trying to do a summarized table with filter and count, but the filter takes out the categories created with O occurrences.

 

 

Table = SUMMARIZECOLUMNS(table[column],Filter(table, table[Custom field (Created)] > today()-30),"Count", Count(table[column]))

 

So want to do something where the table(column) values are kept as the first column regardless if the filter > today()-30 takes it out (nothing in that period) as want to display the zero.

 

 

thank you!

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hello @remi

 

To ensure you keep all values of table[column], you will need to make a few changes:

  1. Remove the FILTER function (2nd argument of SUMMARIZECOLUMNS). This is because if a filterTable is provided, it pre-filters the groupby_ColumNames.
  2. Instead, wrap COUNT ( table[column] ) in CALCULATE, with the FILTER function above placed as the 2nd argument of CALCULATE.
  3. Either wrap this new CALCULATE expression in an IGNORE function, to ensure that blank results are not filtered out, or add zero to ensure there is always a value.

This results in the expressions below. The first one displays blanks when all rows are filtered out and the second one displays zeros.

 

 

Table =
SUMMARIZECOLUMNS (
    table[column],
    "Count", IGNORE (
        CALCULATE (
            COUNT ( table[column] ),
            FILTER ( table, table[Custom field (Created)] > TODAY () - 30 )
        )
    )
)
Table =
SUMMARIZECOLUMNS (
    table[column],
    "Count", CALCULATE (
        COUNT ( table[column] ),
        FILTER ( table, table[Custom field (Created)] > TODAY () - 30 )
    )
        + 0
)

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

6 REPLIES 6
OwenAuger
Super User
Super User

Hello @remi

 

To ensure you keep all values of table[column], you will need to make a few changes:

  1. Remove the FILTER function (2nd argument of SUMMARIZECOLUMNS). This is because if a filterTable is provided, it pre-filters the groupby_ColumNames.
  2. Instead, wrap COUNT ( table[column] ) in CALCULATE, with the FILTER function above placed as the 2nd argument of CALCULATE.
  3. Either wrap this new CALCULATE expression in an IGNORE function, to ensure that blank results are not filtered out, or add zero to ensure there is always a value.

This results in the expressions below. The first one displays blanks when all rows are filtered out and the second one displays zeros.

 

 

Table =
SUMMARIZECOLUMNS (
    table[column],
    "Count", IGNORE (
        CALCULATE (
            COUNT ( table[column] ),
            FILTER ( table, table[Custom field (Created)] > TODAY () - 30 )
        )
    )
)
Table =
SUMMARIZECOLUMNS (
    table[column],
    "Count", CALCULATE (
        COUNT ( table[column] ),
        FILTER ( table, table[Custom field (Created)] > TODAY () - 30 )
    )
        + 0
)

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

I know this has been years but how do i stop power bi from filtering out 0 values from my table when i summarise (count) data? I have values of 0 and 1 and i want them all to be displayed instead of all 0 values filtered out. 

When I put this formula?  as  measure or  calculated column?

Anonymous
Not applicable

@camilocruz table

thanks!!  this  is a query  blank  with formula?

thank you @OwenAuger.

 

Much appreciated the dax!

 

 

Kind regards

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.