Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Thanks Rodney
Solved! Go to 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 ) )
@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 =
@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]
No it is a static value from a database no calcultion
@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
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
Can you please share the complete screenshot of your table visual?
I guess the count will be displayed on a card?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
21 | |
20 | |
14 | |
13 |
User | Count |
---|---|
43 | |
37 | |
25 | |
24 | |
23 |