Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
zenton
Helper II
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]
)
)
zenton_1-1649834685634.png

 Thanks Rodney

1 ACCEPTED SOLUTION

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

View solution in original post

17 REPLIES 17
tamerj1
Super User
Super User

@zenton 
I hope this one works 

Complete Task Count =
VAR SummaryTable =
    ADDCOLUMNS (
        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 ) )

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

Thanks, Rodney

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

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

@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

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

@zenton 
I guess one mistake. Try this

Complete Task Count =
VAR SummaryTable =
    ADDCOLUMNS (
        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 ) )

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

zenton
Helper II
Helper II

@tamerj1 

 

zenton_0-1649841967853.png 

zenton_1-1649842136806.png

 

Yes the Count will be displayed on Card or Text box

Thanks Rodney

@zenton 
What is value? is it measure 3?

No it is a static value from a database no calcultion

@zenton 

So how is this visual related to the count measure?

@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

tamerj1
Super User
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 )
    )

Hi @tamerj1 

The measure4 gave me a result of 90

The MaxValue calculted to 1 not 18

I will give some more information.

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

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

 

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

@zenton 

Can you please share the complete screenshot of your table visual?

I guess the count will be displayed on a card?

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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