March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
62 | |
30 | |
25 | |
22 | |
18 |