Need Help to use unique values of a measure as a row value then count unique id against that row

[Yaseen_Nadaf]

I have a raw table name as Query1 which contains logs of call status for multiple students.  It has 4 columns student_id, enquire_date, call_status, date_added. One student_id has only 1 enquire_date but it may have many different date_added and call_status.

i have to create a visual where 2 slicer options needs to be given one for enquire_date one for disposition_date. Dispostion date is basically the date for which we need to check the latest availaible call_status of student_id.

for eg if i select enquire_date as 1 Oct 2024 then and dispostion_date as 3 Oct 2024, the table should show the latest availaible call status for 1 Oct 2024 enquired students whose latest date_added can be 3 Oct 2024 or before.
Data Tables:

CalenderTable is a calculated table which i have created which just has calender dates and it is not in relationship with Query1. Date from Calender Table I will be using as Disposition Date.
Call Status for Latest Date and Latest Availaible Date Added are the two dynamic measures which I have created which are using 2 slicers as input.

Measure 1 Calculation:
Call Status for Latest Date =

CALCULATE(

    MAX(Query1[call_status]),

    FILTER(

        Query1,

        Query1[date_added] =

        CALCULATE(

            MAX(Query1[date_added]),

            FILTER(

                Query1,

                Query1[date_added] <= SELECTEDVALUE(CalendarTable[Date]) &&

                NOT(ISBLANK(Query1[call_status])) -- Exclude rows where call_status is null

            )

        ) &&

        NOT(ISBLANK(Query1[call_status])) -- Ensure call_status is non-null in the outer filter

    )

)

Measure 2 Calculation:

Latest Available Date Added =

CALCULATE(

    MAX(Query1[date_added]),

    FILTER(

        Query1,

        Query1[date_added] <= SELECTEDVALUE(CalendarTable[Date]) && -- Compare dates

        NOT(ISBLANK(Query1[call_status])) -- Exclude rows where call_status is null

    )

)

This is the output and table is showing correct data:

I have to export this table as excel and then apply a pivot to check the call status and unique student count aggregated.

I need a way to create same visual in power BI without exporting it in excel but unable to do it as I cannot use measures as a row.

[danextian]

Hi @Yaseen_Nadaf 

Can you please post a workable sample data (not an image) and your expected result from that.

[Yaseen_Nadaf]

https://docs.google.com/spreadsheets/d/12tMw23sjm7JGqthQs1OzkGvBOP3aW50m/edit?usp=sharing&ouid=11639... 

Please find the raw data here.

[danextian · Accepted solution]

Hi @Yaseen_Nadaf 

These are the measures I've come up with

Latest Date = 
VAR DispositionDate =
    MAX ( CalendarTable[Date] )
RETURN
    CALCULATE (
        MAX ( Query1[date_added] ),
        ALLEXCEPT ( Query1, Query1[student_id] ),
        KEEPFILTERS ( INT ( Query1[date_added] ) <= DispositionDate )
    )


Count by Status = 
VAR _latestdate = [Latest Date]
RETURN
    COUNTROWS (
        FILTER (
            ADDCOLUMNS (
                SUMMARIZE (
                    Query1,
                    Query1[student_id],
                    Query1[call_status],
                    Query1[date_added]
                ),
                "@latest date", [Latest Date]
            ),
            [@latest date] = [date_added]
        )
    )

Please note that i wrapped date_added with INT as it is a date_time and you're comparing it against a date so essentially, with your current logic, <= will exclude anything beyond 12:00 AM

How do you handle these duplicate records?

Count by status measure counts onlyn once.

[Yaseen_Nadaf]

Thanks a lot man @danextian. This worked perfectly fine. I removed the duplicate rows and null call status values from source itself.
and for a unique date added i considered only one latest row as per time for that day.

Able to get the desired output in same view as expected. Thanks a lot. Was not able to solve this problem since months even after using Chat GPT. 

You are genius!!

[Yaseen_Nadaf]

Can you suggest any way please?

 @Jai-Rathinavel @danextian @Bibiano_Geraldo @rajendraongole1 

[Bibiano_Geraldo]

Hi @Yaseen_Nadaf ,

Can you please share a sample pbi file with no sensitive data? please.

[Yaseen_Nadaf]

https://drive.google.com/file/d/1qG6UW74cCXgoiETFAsBwWVUsvffHN5yI/view?usp=drive_link

Please find the pbi file here: