Count records with Measure as a Filter

[ragnezza]

Hello - I got stuck in something that I thought was simple.

I have a simple table that looks like this:

Date	ID
Jan 3	ABC001
Jan 3	ABC002
Jan 2	ABC002
Jan 1	ABC001
Jan 1	ABC003

This table is filtered based on the selections made on 2 date slicers (Date 1 and Date 2). The selection on Date 1 will output only the date selected, while the selection on Date 2 will create a flag if the ID is in both selected days.
For example, if I apply the following selections Date 1 = Jan 3 and Date 2 = Jan 2, the table will look like this:

Date	ID	Flag
Jan 3	ABC001	No
Jan 3	ABC002	Yes

ABC001 is not on Jan 2, that's why Flag = No.

How can I count only the records where Flag = No ?
I thought it was as simple as combining CALCULATE or COUNTROWS and FILTER, but considering that Flag is a Measure I cannot make it work.

Thanks in advance!

Davide

[Anonymous · Accepted solution]

Hi  @ragnezza ,

According to the official documentation a similar error message describes that you need to try to filter the visual objects to reduce the amount of data in the results.

Refer to:

Troubleshooting tile errors - Power BI | Microsoft Learn

And you can follow the tips to optimize your report：

1.Do not expose in a view a column that is not necessary in the Power BI data model.

2.Optimize the DAX calculation used in this visual to use less memory and return faster or change what the visual displays.

3.Remove unnecessary precision or split granularity values to reduce cardinality（Significantly reduce highly unique datetime values by splitting the date and time into separate columns. ）

This is the related document, you can view this content:

how to fix visual has exceeded the available resource. - Microsoft Q&A

Solved: This visual has exceeded the available resources -... - Microsoft Fabric Community

Resources Exceeded This visual has exceeded the av... - Power Platform Community (microsoft.com)

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.

[ragnezza]

Thanks for looking into it @Anonymous , but I'm getting the following error:

Resources Exceeded
This visual has exceeded the available resources. Try filtering to decrease the amount of data displayed.

[Anonymous]

Hi  @ragnezza ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _mindate=MINX(ALLSELECTED('Table'),[Date])
var _maxdate=MAXX(ALLSELECTED('Table'),[Date])
var _column=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[ID]=MAX('Table'[ID])),"Date",[Date])
RETURN
IF(
    _mindate in _column && _maxdate in _column,"Yes","No")

Measure 2 =
COUNTX(
    FILTER(ALLSELECTED('Table'),'Table'[Measure]="No"),[ID])

2. Result:

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