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
Anonymous
Not applicable

count with and without filters

Hi Community!

I'm having an issue with a calculation.

The difficultity of this one is that I need to make a calculation involving the same column twice, once with filters and one with other filters.

I have a table called bi_events.

This table has several rows. The 3 relevant columns are event_id, robot_id and last_update(this is a date).

The report has 2 filters, one that filters by robot_id and the other one by last_update, so the user can select an event and a timeframe.

After the selection was made, I want to show the percent of robots that had that event in that timeframe, within all the robots that had events in that time frame. So I need 2 numbers: the count of unique robot_id applying both filters and the count of unique robot_id applying only the timeframe filter.

I'm using this formula: 

PercOfRobots = COUNTROWS(DISTINCT(bi_events[robot_id]))/CALCULATE(COUNTROWS(DISTINCT(bi_events[robot_id])),ALLEXCEPT(bi_events,bi_events[last_update]))

 

But is not working, more exactly the second part is not working: CALCULATE(COUNTROWS(DISTINCT(bi_events[robot_id])),ALLEXCEPT(bi_events,bi_events[last_update]))

 

Any ideas why??

Thanks!!

 

PercOfRobots = COUNTROWS(DISTINCT(bi_events[robot_id]))/CALCULATE(COUNTROWS(DISTINCT(bi_events[robot_id])),ALLEXCEPT(bi_events,bi_events[last_update]))

3 REPLIES 3
JosefPrakljacic
Solution Sage
Solution Sage

I guess this will work...

 

If not may I ask you to provide some sample data?

 

PercOfRobots =
COUNTROWS ( DISTINCT ( bi_events[robot_id] ) )
    / CALCULATE (
        COUNTROWS ( DISTINCT ( bi_events[robot_id] ) ),
        REMOVEFILTERS(bi_events[robot_id])
    )

If this post was helpful may I ask you to mark it as solution and give it some kudos?

Have a nice day!

BR,
Josef

Anonymous
Not applicable

Hi,

There are 2 issues with that option:

- It would take all the filters off, but I need to leave the timeframe filters (on the column last_update) and take off only the filter on the event_id.

-The function REMOVEFILTERS is not recognized in my PowerBI.

1.png

 

Thanks!!

1.) Understand

2.) Replace RemoveFIlters with ALL

3.) Please Download the new version of Power BI

 

If its still not working may I ask you to provide us with some copyable sample data.

 

BR,

Josef

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.

Top Solution Authors