count with and without filters

[Anonymous]

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]))

[JosefPrakljacic]

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]

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.

Thanks!!

[JosefPrakljacic]

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