cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

Count with criteria and slicer selection

Hi All,

I want to get the count of people less than 50 for each date based on the criteria as filter option.

If i select criteria 1,2 &3 then i need the count of "id" that has sum of total less than "50"

Output :

 Date total<=50 01-Jan-21 3 07-Jan-21 2 14-Jan-21 2

 id Date Criteria Total a 01-01-2021 1 110 a 07-01-2021 1 110 a 14-01-2021 1 101 b 01-01-2021 2 45 b 01-01-2021 3 45 b 07-01-2021 2 45 b 07-01-2021 3 45 b 14-01-2021 2 45 b 14-01-2021 3 45 c 01-01-2021 4 72 c 01-01-2021 1 2.5 c 07-01-2021 4 40 c 07-01-2021 3 20 c 07-01-2021 2 10 c 07-01-2021 1 2.5 c 14-01-2021 4 90 c 14-01-2021 1 2.5 d 01-01-2021 1 45 d 07-01-2021 1 90 d 14-01-2021 1 90 e 01-01-2021 4 48.3 e 01-01-2021 1 18 e 01-01-2021 5 24.7 e 07-01-2021 4 53.3 e 07-01-2021 1 18 e 07-01-2021 5 24.7 e 14-01-2021 4 48.3 e 14-01-2021 1 18 e 14-01-2021 5 24.7

Need help

2 ACCEPTED SOLUTIONS
Super User

Hi, @hiren89vora

Please check the below picture and the sample pbix file's link down below.

ID count Total Less than 50 =
SUMX (
VALUES ( 'Table'[Date] ),
COUNTROWS (
FILTER ( VALUES ( 'Table'[id] ), CALCULATE ( SUM ( 'Table'[Total] ) ) <= 50 )
)
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Super User

@hiren89vora

You can use the following measure to get the desired results:

``````Measure =
COUNTROWS(
FILTER(
VALUES(Table1[id]),
"Sum", CALCULATE(SUM(Table1[Total]))
),
[Sum] <= 50
)
)``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
4 REPLIES 4
Super User

@hiren89vora

You can use the following measure to get the desired results:

``````Measure =
COUNTROWS(
FILTER(
VALUES(Table1[id]),
"Sum", CALCULATE(SUM(Table1[Total]))
),
[Sum] <= 50
)
)``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
Super User

Hi, @hiren89vora

Please check the below picture and the sample pbix file's link down below.

ID count Total Less than 50 =
SUMX (
VALUES ( 'Table'[Date] ),
COUNTROWS (
FILTER ( VALUES ( 'Table'[id] ), CALCULATE ( SUM ( 'Table'[Total] ) ) <= 50 )
)
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Super User

@hiren89vora , based on what I got. Please try a measure like

sumx(filter(summarize(Table, Table[Date], "_1", sum(Table[Total])),[_1]<=50),[_1])

Frequent Visitor

@amitchandak thanks for the response but i got blank as output

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors