Use the Date from a measure to look into data table and return a count of values from that table

[PaulHallam]

I have a couple of measures.

//Measure 1 calculates the count of "high" and "medium"per day and returns the maximum

Measure 1 =

MAXX(

    KEEPFILTERS(VALUES('Data'[Date])),

    CALCULATE(COUNTA('Data'[Category]),OR ('Data'[Category]="High",(Data[Category]="Medium")

)))

//Measure 2 calculates the date of the highest value from measure 1

Measure 2 =

CALCULATE(max(Data[Date]),TOPN(1,allselected(Data[Date]),[H&MbyDate] ,DESC),VALUES(Data[Date]))

I want to look this date up in another table and return the value in another column.

I want to use the date from measure 2 to look into the data table and return the count of just "Medium" on that day. I can't find a way to use the date to do this, is there something wrong with the logic of what i am doing? Are the above measures leading me down the wrong road?

[Anonymous · Accepted solution]

Hi @PaulHallam 

The following testing is for your reference.

My sample:

Data table:

another table:

Create a calculated column as follows

Column = 
IF (
    'another table'[Date] = [Measure2]
        && 'another table'[Category] = "Medium",
    CALCULATE (
        COUNT ( 'another table'[Category] ),
        FILTER ( 'another table', [Date] = [Measure2] && [Category] = "Medium" )
    )
)

Output:

If this sample data is structurally different from the one you are using, please provide some sample data and the desired output based on the sample data so that I can help you better. How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulHallam]

Measure 2 should read

CALCULATE(max(Data[Date]),TOPN(1,allselected(Data[Date]),[Measure1] ,DESC),VALUES(Data[Date]))