cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

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

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors