Is it possible to convert DAX functions into a Measure so I can filter with a slicer?

Question

I'm just getting my feet wet with DAX and the like, and I know the solution I'm looking for is probably very simple and elegent, but it's at the very edge of my knowledge.

I really hope you can help me out :)

The data I'm working with is a large list of claims.

The data gives a Timestamp when a 'Claim ID' is 'Ready for Review' and 'Complete'

Because the nature of the business, sometimes a claim goes back into 'Ready for Review' after it's been 'Complete'ed, so some 'Claim ID's can have multiple lines that are marked 'Ready for Review'.

Right now I have three DAX functions to calculate if a claim is currently 'Ready for Review'

Columns and such:

RFE State History is the table

Timestamp gives the date and time down to the second

Claim Id is the claim number

Status Label will either be "Ready for Review" or "Complete"

MaxDate: =
CALCULATE (
    MAX ( 'RFE State History'[Timestamp] ),
    FILTER (
        'RFE State History',
        'RFE State History'[Claim Id] = EARLIER ( 'RFE State History'[Claim Id] )
    )
)

This filters by claim number, then outputs the latest date of the lot.

IsLatest: =
IF('RFE State History'[Timestamp]='RFE State History'[MaxDate],"Latest")

When the MaxDate value equals the date on this line, output "Latest"

Ready for Review: =
IF (
    'RFE State History'[Status Label] = "Ready for Review"
        && 'RFE State History'[IsLatest] = "Latest",
    1
)

When the column IsLatest is "Latest", output "1"

I then use 'Redy for Review' in a Pivot Table to sum up how many claims are Ready to Review at that particular moment.

What I need to do from here:

I want to be able to use a Slicer to tell how many claims were Ready to Reiew on any particular day in the past (and seperately: in each hour of the day as well so we can track progress throughout the day).

After Googling about for a couple weeks I ran across these:

~Is it possible to use a slicer as a parameter to a DAX Summarize function?

~DAX Fridays! #93: MAXX | Fill down with DAX and Power Query (YouTube)

~This blog entry on SUMMARIZECOLUMNS

~This PowerBI Post on SUMMARIZECOLUMNS

Which leads me to believe I need to write a Measure that basically does the three DAX functions (MaxDate, IsLatest, and Ready for Review) all at the same time using FILTER and SUMMARIZECOLUMNS so they can be filtered with a Slicer.

Like I said up top there: I'm pretty sure the solution is really simple; I've tried my hand at adapting them to a Measure with no luck. I am only really starting in the DAX world, so I know there's a lot I don't know and don't have a proper grasp on quite yet.

I am really hoping you can help me out here by pointing me in the right direction.

Thanks in advance :)

Jelle Hoekstra Jelle Hoekstra · Accepted Answer · 2019-07-22T09:41:20

If I understand you correctly, you want to count the number of Ready for Review claims at a given day or hour you selected with a slicer?

If that is the case you can can:

Create a new calculated table with the following code. You can find it right next to the button for a calculated column in the modelling menu

DateTime = 
ADDCOLUMNS (
    CROSSJOIN (
        CALENDAR ( DATE ( 2000, 1, 1 ), DATE ( 2025, 12, 31 ) ),
        UNION (
            ROW ( "Time", TIME ( 1, 0, 0 ) ),
            ROW ( "Time", TIME ( 2, 0, 0 ) ),
            ROW ( "Time", TIME ( 3, 0, 0 ) ),
            ROW ( "Time", TIME ( 4, 0, 0 ) ),
            ROW ( "Time", TIME ( 5, 0, 0 ) ),
            ROW ( "Time", TIME ( 6, 0, 0 ) ),
            ROW ( "Time", TIME ( 7, 0, 0 ) ),
            ROW ( "Time", TIME ( 9, 0, 0 ) ),
            ROW ( "Time", TIME ( 10, 0, 0 ) ),
            ROW ( "Time", TIME ( 11, 0, 0 ) ),
            ROW ( "Time", TIME ( 12, 0, 0 ) ),
            ROW ( "Time", TIME ( 13, 0, 0 ) ),
            ROW ( "Time", TIME ( 14, 0, 0 ) ),
            ROW ( "Time", TIME ( 15, 0, 0 ) ),
            ROW ( "Time", TIME ( 16, 0, 0 ) ),
            ROW ( "Time", TIME ( 17, 0, 0 ) ),
            ROW ( "Time", TIME ( 18, 0, 0 ) ),
            ROW ( "Time", TIME ( 19, 0, 0 ) ),
            ROW ( "Time", TIME ( 20, 0, 0 ) ),
            ROW ( "Time", TIME ( 21, 0, 0 ) ),
            ROW ( "Time", TIME ( 22, 0, 0 ) ),
            ROW ( "Time", TIME ( 23, 0, 0 ) ),
            ROW ( "Time", TIME ( 24, 0, 0 ) )
        )
    ),
    "DateTime", [Date] + [Time],
    "Hour", HOUR ( [Time] )
)

Thanks for the answer in this post: https://community.powerbi.com/t5/Desktop/how-to-build-a-calendar-table-with-date-and-time/td-p/241728

Create a measure for you previous calculations

Ready for Review = 
var _selectedPeriod = MAX ( DateTime[DateTime] ) // Grab DateTime from the selected value in DateTime. This can be a single value or the end of a period. Default is last available date in DateTime
RETURN
    COUNTX (
        'RFE State History' ,
        var _maxDate =
            CALCULATE(
                MAX ( 'RFE State History'[Timestamp] ) ,
                ALL ( 'RFE State History' ) , 
                'RFE State History'[Claim Id] = EARLIER( 'RFE State History'[Claim Id] ) ,
                'RFE State History'[Timestamp] <= _selectedPeriod             
            )

        var _isLast = 
            _maxDate = 'RFE State History'[Timestamp]

        RETURN
            IF( AND ( _isLast = TRUE() , 'RFE State History'[Status Label] = "Ready for Review" ) , 1 )
    )

Create a slicer using DateTime from your calendar table and have a visual with the above produced measure

Is it possible to convert DAX functions into a Measure so I can filter with a slicer?

1 Answers