Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
ID | Dates | Average | |
3 | 01-10-2022 | 0 | |
1 | 01-10-2022 | 110 | |
2 | 02-10-2022 | 74 | |
4 | 01-10-2022 | 91 | |
1 | 02-10-2022 | 100 | |
2 | 01-10-2022 | 0 | |
4 | 02-10-2022 | 0 | |
1 | 04-10-2022 | 0 | |
2 | 04-10-2022 | 0 | |
3 | 04-10-2022 | 216 | |
5 | 01-10-2022 | 0 |
Solved! Go to Solution.
Hi, @Laxman007
You can try the following methods.
Measure =
CALCULATE (
DISTINCTCOUNT ( 'Table'[ID] ),
FILTER ( ALL ( 'Table' ), [Dates] = MAX ( 'Table'[Dates] ) && [Average] = 0 )
)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Could you try something like this....
First, create an expression that will return the distinct count of ID values where the count of rows with zero values in the Average column is greater than 0:
DistinctCountOfID =
CALCULATE(
DISTINCTCOUNT(Table[ID]),
FILTER(
Table,
CALCULATE(
COUNT(Table[Average]),
Table[Average] = 0
) > 0
)
)
Then, get the last date, you can use the MAX function. Here's an example of how you can use it:
LastDate =
MAX(Table[Dates])
Finally, combine these measures, you can create a new measure that uses the values returned by these measures. For example:
CombinedMeasure =
"Last Date: " & LastDate & ", Distinct Count of ID: " & DistinctCountOfID
This will create a new measure called "CombinedMeasure" that will display the last date and the distinct count of ID values as a string.
thanks for update , but value needed for last date only
@Laxman007
Ok but the same ID/Date combination is duplicated many times. There must be another column involved in the filter context of table. Please advise
ok its a sample table. my mistake
i need last date zero count , and disticnt value count from id.
i have three measure also seperately, how to join all , (last dates , distict count by id and countrows =0)
as per below table answer should be 2
ID | Dates | Average | |
3 | 01-10-2022 | 0 | |
1 | 01-10-2022 | 110 | |
2 | 02-10-2022 | 74 | |
4 | 01-10-2022 | 91 | |
1 | 02-10-2022 | 100 | |
2 | 01-10-2022 | 0 | |
4 | 02-10-2022 | 0 | |
1 | 04-10-2022 | 0 | |
2 | 04-10-2022 | 0 | |
3 | 04-10-2022 | 216 | |
5 | 01-10-2022 | 0 |
Hi, @Laxman007
You can try the following methods.
Measure =
CALCULATE (
DISTINCTCOUNT ( 'Table'[ID] ),
FILTER ( ALL ( 'Table' ), [Dates] = MAX ( 'Table'[Dates] ) && [Average] = 0 )
)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Laxman007
Please try
Zero Average Cout =
SUMX (
VALUES ( 'Table'[ID] ),
VAR CurrentIDTable =
CALCULATETABLE ( 'Table' )
VAR MaxDate =
MAXX ( CurrentIDTable, 'Table'[Date] )
VAR AverageTable =
ADDCOLUMNS ( CurrentIDTable, "@Average", [Average] )
VAR ZeroAverageTable =
FILTER ( AverageTable, [@Average] = 0 )
VAR LastZeroDate =
MAXX ( ZeroAverageTable, 'Table'[Date] )
RETURN
IF ( LastZeroDate = MaxDate, 1 )
)
Hi @Laxman007
If [Average] is a measure you can try
Zero Average Cout =
COUNTROWS (
DISTINCT (
FILTER (
SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[Date], "@Average", [Average] ),
--or SUMMARIZE ( 'Table', 'Table'[ID], 'Date'[Date], "@Average", [Average] ),
[@Average] = 0
)
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
44 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
70 | |
49 | |
45 | |
20 | |
16 |