topic Re: Count from summarised row count in DAX Commands and Tips

Count from summarised row count

zenton — Wed, 13 Apr 2022 07:27:54 GMT

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

Re: Count from summarised row count

tamerj1 — Wed, 13 Apr 2022 07:38:50 GMT

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

Re: Count from summarised row count

zenton — Wed, 13 Apr 2022 08:10:26 GMT

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

Re: Count from summarised row count

tamerj1 — Wed, 13 Apr 2022 08:28:16 GMT

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

Re: Count from summarised row count

zenton — Wed, 13 Apr 2022 08:39:40 GMT

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

Re: Count from summarised row count

tamerj1 — Wed, 13 Apr 2022 09:01:23 GMT

zenton

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

I guess the count will be displayed on a card?

Re: Count from summarised row count

zenton — Wed, 13 Apr 2022 09:29:20 GMT

tamerj1

Yes the Count will be displayed on Card or Text box

Thanks Rodney

Re: Count from summarised row count

tamerj1 — Wed, 13 Apr 2022 09:35:08 GMT

zenton
What is value? is it measure 3?

Re: Count from summarised row count

zenton — Wed, 13 Apr 2022 09:41:56 GMT

No it is a static value from a database no calcultion

Re: Count from summarised row count

tamerj1 — Wed, 13 Apr 2022 10:11:27 GMT

zenton

So how is this visual related to the count measure?

Re: Count from summarised row count

zenton — Wed, 13 Apr 2022 10:31:16 GMT

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

Re: Count from summarised row count

tamerj1 — Wed, 13 Apr 2022 10:49:55 GMT

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

Re: Count from summarised row count

zenton — Wed, 13 Apr 2022 11:07:47 GMT

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

Thanks, Rodney

Re: Count from summarised row count

tamerj1 — Wed, 13 Apr 2022 11:17:05 GMT

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

Re: Count from summarised row count

zenton — Wed, 13 Apr 2022 11:32:44 GMT

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]

Re: Count from summarised row count

tamerj1 — Wed, 13 Apr 2022 12:28:14 GMT

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

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

Re: Count from summarised row count

zenton — Wed, 13 Apr 2022 12:48:02 GMT

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

Re: Count from summarised row count

tamerj1 — Wed, 13 Apr 2022 13:43:19 GMT

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