Forum Discussion

pbrainard's avatar
pbrainard
Icon for Helper III rankHelper III
4 years ago
Solved

Measure not able to be used as Filter

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 Car...
Need Help
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    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's avatar
    bcdobbs
    4 years ago

    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's avatar
pbrainard
Icon for Helper III rankHelper III
4 years ago

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's avatar
bcdobbs
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
    bcdobbs
    Icon for Community Champion rankCommunity Champion
    4 years ago

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