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

        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]

    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

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

        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