Count from summarised row count

[zenton]

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

[tamerj1]

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

[zenton]

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

Thanks, Rodney

[tamerj1]

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

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

[zenton]

@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

[tamerj1 · 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 ) )

[tamerj1]

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

[zenton]

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

@tamerj1 

 

Yes the Count will be displayed on Card or Text box

Thanks Rodney

[tamerj1]

@zenton 
What is value? is it measure 3?

[zenton]

No it is a static value from a database no calcultion

[tamerj1]

@zenton 

So how is this visual related to the count measure?

[zenton]

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

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

[zenton]

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

[tamerj1]

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

[zenton]

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

[tamerj1]

@zenton 

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

I guess the count will be displayed on a card?