Calculate where measure does not equal to value

[Anonymous]

Hi Experts

The following DAX measure work fine, i have two version one for upward moving values and the other for downward moving values (data points). i would like to work out the 3rd option when No movement is selected from my slicer (so basically it does not equal to either up nor downwards)

Downwards Measure

Downwards = CALCULATE (
    IF (
        ISBLANK ( COUNTA ( 'PMS_COMPLAINT'[MATERIAL_ID]) ),
        BLANK (),
        COUNTA ( 'PMS_COMPLAINT'[MATERIAL_ID] )
    ),
   FILTER (
        ALLSELECTED ('PMS_FINANCIAL_PDS'[ROLL12_COMPLETE_PDS_IND] ),
        CALCULATE('_Measures'[p-value]<=0.05 && 'PMS_FINANCIAL_PDS'[Tau 1] < 0)
    )
) + 0

Upwards Measure

Upwards = CALCULATE (
    IF (
        ISBLANK ( COUNTA ( 'PMS_COMPLAINT'[MATERIAL_ID]) ),
        BLANK (),
        COUNTA ( 'PMS_COMPLAINT'[MATERIAL_ID] )
    ),
   FILTER (
        ALLSELECTED ('PMS_FINANCIAL_PDS'[ROLL12_COMPLETE_PDS_IND] ),
        CALCULATE('_Measures'[p-value]<=0.05 && 'PMS_FINANCIAL_PDS'[Tau 1] > 0)
    )
) + 0

the key is the '_Measures'[p-value]<=0.05 && 'PMS_FINANCIAL_PDS'[Tau 1] > 0) part which tell you direction of movement.

[Anonymous · Accepted solution]

I would make a measure like the one below - it should filter the opposite observations than upward and downwards:

The logic: 

• if the p-value is above 0,05 it will not be in either up or down, thus these rows are included.
• if the Tau 1 is equal to 0 it will not be included in the up or down.

If either of the two is true then the row is included in the dataset. 

No change = CALCULATE (
    IF (
        ISBLANK ( COUNTA ( 'PMS_COMPLAINT'[MATERIAL_ID]) ),
        BLANK (),
        COUNTA ( 'PMS_COMPLAINT'[MATERIAL_ID] )
    ),
   FILTER (
        ALLSELECTED ('PMS_FINANCIAL_PDS'[ROLL12_COMPLETE_PDS_IND] ),
        CALCULATE('_Measures'[p-value] > 0.05 || 'PMS_FINANCIAL_PDS'[Tau 1] = 0)
    )
) + 0

If this works then please mark it as the solution and else please provide some more information about your issue, i.e. a small sample of the data.

[Anonymous]

All Experts

Many thanks for the excellent feedback.

[Mariusz]

Hi @Anonymous 

Try.

Downwards = CALCULATE (
    IF (
        ISBLANK ( COUNTA ( 'PMS_COMPLAINT'[MATERIAL_ID]) ),
        BLANK (),
        COUNTA ( 'PMS_COMPLAINT'[MATERIAL_ID] )
    ),
   FILTER (
        ALLSELECTED ('PMS_FINANCIAL_PDS'[ROLL12_COMPLETE_PDS_IND] ),
        CALCULATE('_Measures'[p-value]<=0.05 && 'PMS_FINANCIAL_PDS'[Tau 1] = 0)
    )
) + 0

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

Apologies for asking can anyone shed some light on

https://community.powerbi.com/t5/Desktop/Relationship-issue-with-Measure/m-p/786447#M378770

its been driving me mad.