cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ekavi
Frequent Visitor

Improving measures by reusing filters

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

2 ACCEPTED SOLUTIONS

Hello @ekavi 
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

View solution in original post

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

ekavi_0-1613008099280.png

 

2) Created a filter menu using the above measure table

ekavi_1-1613008203726.png

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,

ekavi_2-1613008933906.png

 

 

View solution in original post

10 REPLIES 10
mahoneypat
Super User IV
Super User IV

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


ekavi
Frequent Visitor

@mahoneypat Thanks for your suggestions

TarunSharma
Resolver III
Resolver III

Hello @ekavi 
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])

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

Hello @ekavi 
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

View solution in original post

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

ekavi_0-1613008099280.png

 

2) Created a filter menu using the above measure table

ekavi_1-1613008203726.png

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,

ekavi_2-1613008933906.png

 

 

View solution in original post

Really appreciate your support!

AntrikshSharma
Super User II
Super User II

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

amitchandak
Super User IV
Super User IV

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

 

Use slicer or page level filter.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak Can you explain with an example pls?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors