Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
Hello @remi
To ensure you keep all values of table[column], you will need to make a few changes:
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
Hello @remi
To ensure you keep all values of table[column], you will need to make a few changes:
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
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?
thanks!! this is a query blank with formula?
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |