Can anyone assist on this dax. i need last date's , distinct id counts of having zero average .

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

thanks for update , but value needed for last date only 

[tamerj1]

@Anonymous 
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

[Anonymous]

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

[v-zhangti · Accepted solution]

Hi, @Anonymous 

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.

[tamerj1]

@Anonymous 
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 )
)

[tamerj1]

Hi @Anonymous 
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
        )
    )
)