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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Laxman007
Helper I
Helper I

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

IDDates Average
301-10-2022 0
101-10-2022 110
202-10-2022 74
401-10-2022 91
102-10-2022 100
201-10-2022 0
402-10-2022 0
104-10-2022 0
204-10-2022 0
304-10-2022 216
501-10-2022 0
1 ACCEPTED SOLUTION

Hi, @Laxman007 

 

You can try the following methods.

 

Measure = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[ID] ),
    FILTER ( ALL ( 'Table' ), [Dates] = MAX ( 'Table'[Dates] ) && [Average] = 0 )
)

 

vzhangti_0-1672739417183.png

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.

View solution in original post

7 REPLIES 7
fseabrook92
Frequent Visitor

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.

Laxman007
Helper I
Helper I

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

1.png

 

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

IDDates Average
301-10-2022 0
101-10-2022 110
202-10-2022 74
401-10-2022 91
102-10-2022 100
201-10-2022 0
402-10-2022 0
104-10-2022 0
204-10-2022 0
304-10-2022 216
501-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 )
)

 

vzhangti_0-1672739417183.png

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 )
)
tamerj1
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors