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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors