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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors