cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper II

## Count from summarised row count

Hi,

I have a measure to Countrows of summarized data. Results in the table below.

I need a measure to show count of the rows that equal 18. So the result should be 4 as per table below

Measure 3 =
COUNTROWS (
SUMMARIZE (

Labware,
Labware[Analyte Name],
Labware[LIMS Text ID],
Labware[Calendar Date]
)
)

Thanks Rodney

1 ACCEPTED SOLUTION
Super User

@zenton
It is sometimes confusing when don't have the actual data. I will go back to your Measure 3 and start from there

``````Relative price =
SUMX ( VALUES ( Labware[Calendar Date] ), IF ( [Measure 3] = 18, 1, 0 ) )``````
17 REPLIES 17
Super User

@zenton
I hope this one works

``````Complete Task Count =
VAR SummaryTable =
SUMMARIZE (
Labware,
Labware[Analyte Name],
Labware[LIMS Text ID],
Labware[Calendar Date]
),
"@Count", COUNTROWS ( Labware )
)
VAR MaxCount =
MAXX ( SummaryTable, [@Count] )
RETURN
SUMX ( SummaryTable, IF ( [@Count] = MaxCount, 1, 0 ) )``````
Helper II

@tamerj1 The Measure gives me a result of 90. This is the total count of all values in this table

Thanks, Rodney

Super User

@zenton
I think I have totally misunderstood the requirement. Would you please try

``````Complete Task Count =
SUMX ( Labware, IF ( ISBLANK ( Labware[Value] ), 0, 1 ) )``````
Helper II

@tamerj1  This still gives me a result of 90. I am exploring using a filter on my existing total count measure
Total Records =

CALCULATE(DISTINCTCOUNT(Labware[Calendar Date]),
ALLSELECTED(Labware[Calendar Date])
)
Total Records measure result is 6
Super User

@zenton
It is sometimes confusing when don't have the actual data. I will go back to your Measure 3 and start from there

``````Relative price =
SUMX ( VALUES ( Labware[Calendar Date] ), IF ( [Measure 3] = 18, 1, 0 ) )``````
Super User

@zenton
I guess one mistake. Try this

``````Complete Task Count =
VAR SummaryTable =
SUMMARIZE (
Labware,
Labware[Analyte Name],
Labware[LIMS Text ID],
Labware[Calendar Date]
),
"@Count", CALCULATE ( COUNTROWS ( Labware ) )
)
VAR MaxCount =
MAXX ( SummaryTable, [@Count] )
RETURN
SUMX ( SummaryTable, IF ( [@Count] = MaxCount, 1, 0 ) )``````
Helper II

@tamerj1 No same result. I am trying to get my head around this measure. If we are not using [Value] in the calculation how can we calculate if there are less than 18 results of [Value]

Helper II

Yes the Count will be displayed on Card or Text box

Thanks Rodney

Super User

@zenton
What is value? is it measure 3?

Helper II

No it is a static value from a database no calcultion

Super User

So how is this visual related to the count measure?

Helper II

@tamerj1  I created the Count measure to try to get count of all records that have incomplete records. But the Count Measure I wrote would only give totals for all records which equaled to 90.

I need a Measure to Count all incomplete records. In the Matrix above, there is 4 complete and 2 incomplete. So I need a measure to show the count of 4

Super User

Hi @zenton
You can try

``````Measure 4 =
VAR MaxValue =
MAXX (
SUMMARIZE (
Labware,
Labware[Analyte Name],
Labware[LIMS Text ID],
Labware[Calendar Date]
),
[Measure 3]
)
RETURN
SUMX (
SUMMARIZE (
Labware,
Labware[Analyte Name],
Labware[LIMS Text ID],
Labware[Calendar Date]
),
IF ( [Measure 3] = MaxValue, 1, 0 )
)``````
Helper II

Hi @tamerj1

The measure4 gave me a result of 90

The MaxValue calculted to 1 not 18

Each date has multiple [LIMS Text ID]

Each LIMS Text ID has a max of 18 [Analyte Name]

Each [Analyte Name] has a [Value]

On some dates not all the [Analyte Name] have a result in [Value]

In the table provided the first 4 dates the [Analyte Name] contains all 18 [Value]

In the last 2 dates the [Analyte Name] contains only 9 [Value]

Thanks Rodney

Super User

@zenton
Ok Then it is easier to hard code it

```Measure 4 =
VAR MaxValue = 18
RETURN
SUMX (
SUMMARIZE (
Labware,
Labware[Analyte Name],
Labware[LIMS Text ID],
Labware[Calendar Date]
),
IF ( [Measure 3] = MaxValue, 1, 0 )
)```

Helper II

Hi @tamerj1 That is exactly what I did but I get a result of 0

This is because Measure3 gives a result of 90. I only get a Count for each date if I use Measure3 in a Table with the date field

Super User

I guess the count will be displayed on a card?

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors