Forum Discussion

zenton's avatar
zenton
Helper II
4 years ago
Solved

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

COUNT
DAX
Measures
SUMMARIZE
  • tamerj1's avatar
    tamerj1
    4 years ago

    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

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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's avatar
      zenton
      Helper II
      4 years ago

      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's avatar
        tamerj1
        Community Champion
        4 years ago

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

         

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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's avatar
      zenton
      Helper II
      4 years ago

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

      Thanks, Rodney

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

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