Improving measures by reusing filters

[Anonymous]

Hello guys,

I have a PBI dashboard with 20+ measures like below and I want to improve my measures as it has an almost similar set of filters,

MEASURE 1 = CALCULATE(DISTINCTCOUNT('vw_w'[ID]),
FILTER('vw_w','vw_w'[SUBTYPE]= "FLUSHING"||'vw_w'[SUBTYPE]= "QUALITY"),
FILTER('vw_w', 'vw_w'[QUESTIONS1]= "Clarity"),
FILTER('vw_w','vw_w'[Closure] ="" ||'vw_w'[Closure] ="Double up"),
FILTER('vw_w','vw_w'[CONTACT] <>"PDC"),
FILTER('vw_w','vw_w'[Activity] <>"Investigate"&&'vw_w'[Activity] <>"Read"))*1000/2500

MEASURE 2 = CALCULATE(DISTINCTCOUNT('vw_w'[ID]),
FILTER('vw_w','vw_w'[SUBTYPE]= "FLUSHING"||'vw_w'[SUBTYPE]= "SEWERAGE"),
FILTER('vw_w', 'vw_w'[QUESTIONS1]= "ODOUR"),
FILTER('vw_w','vw_w'[Closure] ="" ||'vw_w'[Closure] ="Double up"),
FILTER('vw_w','vw_w'[CONTACT] <>"PDC"),
FILTER('vw_w','vw_w'[Activity] <>"Investigate"&&'vw_w'[Activity] <>"Read"))*1000/2500

MEASURE 3 = CALCULATE(DISTINCTCOUNT('vw_w'[ID]),
FILTER('vw_w','vw_w'[RESOLVE_Time]<=10),
FILTER('vw_w','vw_w'[SUBTYPE]= "FLUSHING"||'vw_w'[SUBTYPE]= "SEWERAGE"),
FILTER('vw_w', 'vw_w'[QUESTIONS1]= "ODOUR"),
FILTER('vw_w','vw_w'[Closure] ="" ||'vw_w'[Closure] ="Double up"),
FILTER('vw_w','vw_w'[CONTACT] <>"PDC"),
FILTER('vw_w','vw_w'[Activity] <>"Investigate"&&'vw_w'[Activity] <>"Read"))/
CALCULATE(DISTINCTCOUNT('vw_w'[ID]),
FILTER('vw_w','vw_w'[SUBTYPE]= "FLUSHING"||'vw_w'[SUBTYPE]= "SEWERAGE"),
FILTER('vw_w', 'vw_w'[QUESTIONS1]= "ODOUR"),
FILTER('vw_w','vw_w'[Closure] ="" ||'vw_w'[Closure] ="Double up"),
FILTER('vw_w','vw_w'[CONTACT] <>"PDC"),
FILTER('vw_w','vw_w'[Activity] <>"Investigate"&&'vw_w'[Activity] <>"Read")

Any suggestions to improve these DAX measures.

thanks

Ekavi

[Anonymous · Accepted solution]

Hello @Anonymous 
You can try these calculations.

Total Count =
DISTINCTCOUNT ( 'vw_w'[ID] )

Base Measure =
CALCULATE (
[Total Count],
'vw_w'[SUBTYPE] = "FLUSHING"
|| 'vw_w'[SUBTYPE] = "SEWERAGE",
'vw_w'[QUESTIONS1] = "ODOUR",
'vw_w'[Closure] = ""
|| 'vw_w'[Closure] = "Double up",
'vw_w'[CONTACT] <> "PDC",
'vw_w'[Activity] <> "Investigate"
&& 'vw_w'[Activity] <> "Read"
)

Measure 2 =
Divide( [Base Measure] * 1000, 2500)

Measure 3 =
VAR _measure =
CALCULATE ( [Base Measure], FILTER ( 'vw_w', 'vw_w'[RESOLVE_Time] <= 10 ) )
VAR __measure = [Base Measure]
RETURN
DIVIDE ( _measure, __measure )

Measure1 =
CALCULATE (
[total count],
'vw_w'[SUBTYPE] = "FLUSHING"
|| 'vw_w'[SUBTYPE] = "QUALITY",
'vw_w'[QUESTIONS1] = "Clarity",
'vw_w'[Closure] = ""
|| 'vw_w'[Closure] = "Double up",
'vw_w'[CONTACT] <> "PDC",
'vw_w'[Activity] <> "Investigate"
&& 'vw_w'[Activity] <> "Read"
) * 1000 / 2500

[mahoneypat]

In addition to the suggestion from @amitchandak , you should avoid filtering the whole table in FILTER.  Just filter the columns you need or use this approach.

New Measure =
CALCULATE (
    DISTINCTCOUNT ( 'vw_w'[ID] ),
    'vw_w'[SUBTYPE]
        IN {
        "FLUSHING",
        "QUALITY"
    },
    'vw_w'[QUESTIONS1] = "Clarity",
    'vw_w'[Closure]
        IN {
        "",
        "Double up"
    },
    'vw_w'[CONTACT] <> "PDC",
    'vw_w'[Activity] <> "Investigate"
        && 'vw_w'[Activity] <> "Read"
) * 1000 / 2500

Pat

[Anonymous]

@mahoneypat Thanks for your suggestions

[Anonymous]

Hello @Anonymous 
Create the measure for the same filter or calculation and use it in your measures.
Like create a measure for distinct count and use measure instead of writing the same calculations.
TotalCount = DISTINCTCOUNT('vw_w'[ID])

[Anonymous]

@Anonymous Sounds really good. Can you please elaborate more with an example?

[Anonymous · Accepted solution]

Hello @Anonymous 
You can try these calculations.

Total Count =
DISTINCTCOUNT ( 'vw_w'[ID] )

Base Measure =
CALCULATE (
[Total Count],
'vw_w'[SUBTYPE] = "FLUSHING"
|| 'vw_w'[SUBTYPE] = "SEWERAGE",
'vw_w'[QUESTIONS1] = "ODOUR",
'vw_w'[Closure] = ""
|| 'vw_w'[Closure] = "Double up",
'vw_w'[CONTACT] <> "PDC",
'vw_w'[Activity] <> "Investigate"
&& 'vw_w'[Activity] <> "Read"
)

Measure 2 =
Divide( [Base Measure] * 1000, 2500)

Measure 3 =
VAR _measure =
CALCULATE ( [Base Measure], FILTER ( 'vw_w', 'vw_w'[RESOLVE_Time] <= 10 ) )
VAR __measure = [Base Measure]
RETURN
DIVIDE ( _measure, __measure )

Measure1 =
CALCULATE (
[total count],
'vw_w'[SUBTYPE] = "FLUSHING"
|| 'vw_w'[SUBTYPE] = "QUALITY",
'vw_w'[QUESTIONS1] = "Clarity",
'vw_w'[Closure] = ""
|| 'vw_w'[Closure] = "Double up",
'vw_w'[CONTACT] <> "PDC",
'vw_w'[Activity] <> "Investigate"
&& 'vw_w'[Activity] <> "Read"
) * 1000 / 2500

[Anonymous · Accepted solution]

Hi All,

I have ended up with the following solution. I wanted to build a dashboard with several measures  (Please see my original post with the first set of measures and I wanted to make them better and efficient). My measures are based on some common filters to the main fact table "vw_w".

1) First, I created a custom measure table (Water Dimensions) like below

2) Created a filter menu using the above measure table

3) Created two common measures to use across the other measures as below,

Total_Count = DISTINCTCOUNT ( 'vw_w'[ID] )

Total_Connections = MAX('vw_w'[RunningTotal])

4) Created one main measure as below to yield the result for each measure dimension. 

Satisfactions =
VAR Filter_Clarity_Taste_Odour = FILTER('vw_w','vw_w'[CC_SUBTYPES_ID]="FLUSHING" || 'vw_w'[CC_SUBTYPES_ID]="QUALITY")

VAR QuestionValue = SELECTEDVALUE('Water Dimensions'[DrinkingMeasures])

VAR Filter_Question= FILTER('vw_w', 'vw_w'[QUESTIONS]= QuestionValue)

VAR Filter_Pressure = FILTER('vw_w', 'vw_w'[SUBTYPES_SK]=298 && 'vw_w'[QUESTIONS]= "LOW PRESSURE"||'vw_w'[SUBTYPES_SK]=293)

VAR Filter_Continuity = FILTER('vw_w', 'vw_w'[SUBTYPES_SK]=298 && 'vw_w'[QUESTIONS]= "NO PRESSURE"||'vw_w'[SUBTYPES_SK]=299)

VAR Filter_Response_Any = FILTER('vw_w','vw_w'[TYPES__ID]= "WATER")

VAR Filter_ISSUE_EXISTANCE = FILTER('vw_w','vw_w'[ISSUE_EXISTANCE]= "EXISTING ISSUE" || 'vw_w'[ISSUE_EXISTANCE]= "EXIS")

VAR Filter_Common = CALCULATETABLE(FILTER('vw_w', 'vw_w'[CONTACT]<> "PDC"), FILTER('vw_w','vw_w'[CLOSURETYPE_DESC] ="UNSPECIFIED" ||'vw_w'[CLOSURETYPE_DESC] ="Double up"), FILTER('vw_w', 'vw_w'[ACTIVITY_DESC] <>"Investigate"&&'vw_w'[ACTIVITY_DESC] <>"Read"))

Return

IF(SELECTEDVALUE('Water Dimensions'[DrinkingMeasures])=BLANK(), 0,

SWITCH( TRUE(),
VALUES('Water Dimensions'[DrinkingMeasures])= "Clarity",
CALCULATE([Total_Count],
Filter_Clarity_Taste_Odour, Filter_Question, Filter_Common)*1000/
CALCULATE([Total_Connections])
,
VALUES('Water Dimensions'[DrinkingMeasures])= "Taste",
CALCULATE([Total_Count],
Filter_Clarity_Taste_Odour, Filter_Question, Filter_Common)*1000/CALCULATE([Total_Connections])
,
VALUES('Water Dimensions'[DrinkingMeasures])= "Odour",
CALCULATE([Total_Count],
Filter_Clarity_Taste_Odour, Filter_Question, Filter_Common)*1000/CALCULATE([Total_Connections])
,
VALUES('Water Dimensions'[DrinkingMeasures])= "Pressure",
CALCULATE([Total_Count],
Filter_Pressure, Filter_Common)*1000/CALCULATE([Total_Connections])
,
VALUES('Water Dimensions'[DrinkingMeasures])= "Continuity",
CALCULATE([Total_Count],
Filter_Continuity, Filter_Common)*1000/CALCULATE([Total_Connections])
,
VALUES('Water Dimensions'[DrinkingMeasures])= "Response_Any",
CALCULATE([Total_Count],
Filter_Response_Any,Filter_ISSUE_EXISTANCE,Filter_Common)*1000/CALCULATE([Total_Connections])
,
VALUES('Water Dimensions'[DrinkingMeasures])= "Connections", [Connections],
BLANK(), BLANK()))

5) The final dashboard is like below,

[Anonymous]

Really appreciate your support!

[AntrikshSharma]

@Anonymous If you are in PBI and connected to SSAS then try DETAILROWS property of a measure: https://dax.guide/detailrows/

[amitchandak]

@Anonymous , You can have a measure with the common filters and then add other filters on top of it

Use slicer or page level filter.

[Anonymous]

@amitchandak Can you explain with an example pls?