Measure not able to be used as Filter

[pbrainard]

I've got this measure which is working in the view. This is a calculated table.

DatesOkay = IF(MAX('CTab'[End Date]) > MAX('CTab'[Start Date]), "YES", "NO")

But when I add a Data Card to do a DISTINCTCOUNT on IDs, and add the DatesOkay measure to the data card's filter, it won't let me do anything with it. I want to be able to filter my data card by DatesOkay = "YES". Clicking on Contains does nothing, and I can't add YES...liked it's locked or something, and it's not.

I'm assuming I have to create the column in the calculated table DAX, but can't seem to get it to work. Here's the table language...

CTab =

ADDCOLUMNS (

ADDCOLUMNS (

SUMMARIZE ( GAD_PHQ, GAD_PHQ[Client_ID], GAD_PHQ[Assess_Type] ),

"Start Date", CALCULATE ( MIN ( GAD_PHQ[Assess_Date] ) ),

"End Date", CALCULATE ( MAX ( GAD_PHQ[Assess_Date] ) )

),

"Start Score",

VAR _current_asset_type = GAD_PHQ[Assess_Type]

VAR _current_start_date = [Start Date]

RETURN

CALCULATE (

AVERAGE ( GAD_PHQ[Assess_Score] ),

GAD_PHQ[Assess_Date] = _current_start_date,

GAD_PHQ[Assess_Type] = _current_asset_type

),

"End Score",

VAR _current_asset_type = GAD_PHQ[Assess_Type]

VAR _current_end_date = [End Date]

RETURN

CALCULATE (

AVERAGE ( GAD_PHQ[Assess_Score] ),

GAD_PHQ[Assess_Date] = _current_end_date,

GAD_PHQ[Assess_Type] = _current_asset_type

)

)

[bcdobbs · Accepted solution]

Adding this into your card visual I think should work:

Distinct Ids With Okay Dates = 
CALCULATE (
    DISTINCTCOUNT ( CTab[Client_Id] ),
    FILTER ( 
        CTab,
        CTab[End Date] > CTab[Start Date]
    )
)

[bcdobbs]

Sorry, misunderstood the question. Will have a play with your dax and get back to you.

[bcdobbs · Accepted solution]

Adding this into your card visual I think should work:

Distinct Ids With Okay Dates = 
CALCULATE (
    DISTINCTCOUNT ( CTab[Client_Id] ),
    FILTER ( 
        CTab,
        CTab[End Date] > CTab[Start Date]
    )
)

[pbrainard]

Like magic. Thank you!!!!

[bcdobbs · Accepted solution]

Actually moving the logic into your calculated table will be more efficient:

CTab =
ADDCOLUMNS (
    ADDCOLUMNS (
        SUMMARIZE ( GAD_PHQ, GAD_PHQ[Client_ID], GAD_PHQ[Assess_Type] ),
        "Start Date", CALCULATE ( MIN ( GAD_PHQ[Assess_Date] ) ),
        "End Date", CALCULATE ( MAX ( GAD_PHQ[Assess_Date] ) )
    ),
    "Start Score",
        VAR _current_asset_type = GAD_PHQ[Assess_Type]
        VAR _current_start_date = [Start Date]
        RETURN
            CALCULATE (
                AVERAGE ( GAD_PHQ[Assess_Score] ),
                GAD_PHQ[Assess_Date] = _current_start_date,
                GAD_PHQ[Assess_Type] = _current_asset_type
            ),
    "End Score",
        VAR _current_asset_type = GAD_PHQ[Assess_Type]
        VAR _current_end_date = [End Date]
        RETURN
            CALCULATE (
                AVERAGE ( GAD_PHQ[Assess_Score] ),
                GAD_PHQ[Assess_Date] = _current_end_date,
                GAD_PHQ[Assess_Type] = _current_asset_type
            ),
	"Dates Okay",
		IF (
			[End Date] > [Start Date],
			"Yes",
			"No"
			)
)

The measure can then filter a plane distinct count or write a measure like:

Distinct Ids With Okay Dates = 
CALCULATE (
    DISTINCTCOUNT ( CTab[Client_Id] ),
    CTab[Dates Okay] = "Yes"
)

[pbrainard]

Disregard!

I need one more calculation to bring this home. 

I need a column that I can filter on that indicates that the End Score is less than the Start Score. I tried to replicate the Dates Okay syntax, but the table DAX isn't recognizing the [End Date] or [Start Date]. When I use "End Date" and "Start Date" they all come back "YES". 

I was able to make another calculation to make this work (not in the table, but...)

Improved = CALCULATE(

DISTINCTCOUNT ( CTab[Client_Id] ),

(CTab[End Score] < CTab[Start Score]))

[bcdobbs]

If you wanted to reference those columns you'd need another nested addcolumns before you do it. In my opinion that starts to get messy so would be better to try and refactor the whole thing using a set of variables. I'm out at the moment but will give it when I'm back.

[bcdobbs]

Hard to test I've not done some silly but I think this is what you want:

CTab =
GENERATE (
    SUMMARIZE ( GAD_PHQ, GAD_PHQ[Client_ID], GAD_PHQ[Assess_Type] ),
    VAR _StartDate =
        CALCULATE ( MIN ( GAD_PHQ[Assess_Date] ) )
    VAR _EndDate =
        CALCULATE ( MAX ( GAD_PHQ[Assess_Date] ) )
    VAR _current_asset_type = GAD_PHQ[Assess_Type]
    VAR _current_start_date = [Start Date]
    VAR _StartScore =
        CALCULATE (
            AVERAGE ( GAD_PHQ[Assess_Score] ),
            GAD_PHQ[Assess_Date] = _current_start_date,
            GAD_PHQ[Assess_Type] = _current_asset_type
        )
    VAR _current_asset_type = GAD_PHQ[Assess_Type]
    VAR _current_end_date = [End Date]
    VAR _EndScore =
        CALCULATE (
            AVERAGE ( GAD_PHQ[Assess_Score] ),
            GAD_PHQ[Assess_Date] = _current_end_date,
            GAD_PHQ[Assess_Type] = _current_asset_type
        )
    VAR _DatesOkay =
        IF ( _EndDate > _StartDate, "Yes", "No" )
    VAR _Improved =
        IF ( _EndScore > _StartScore, "Yes", "No" )
    RETURN
        ROW (
            "Start Date", _StartDate,
            "End Date", _EndDate,
            "Start Score", _StartScore,
            "End Score", _EndScore,
            "Dates Okay", _DatesOkay,
            "Improved", _Improved
        )
)

[pbrainard]

Even better. They both work, but I like the idea of integrating into the table. Thank you!!!!!

[bcdobbs]

Try returning 1 and 0 instead of "Yes", "No". For some reason you can only filter in integers.

[pbrainard]

I can filter on the table without issue, but I can't apply the DatesOkay filter to a data card to get a count of IDs where DatesOkay = YES.