Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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

View solution in original post

Anonymous
Not applicable

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

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


Anonymous
Not applicable

@mahoneypat Thanks for your suggestions

Anonymous
Not applicable

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
Not applicable

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

Anonymous
Not applicable

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
Not applicable

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

 

 

Anonymous
Not applicable

Really appreciate your support!

AntrikshSharma
Community Champion
Community Champion

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

amitchandak
Super User
Super User

@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
Not applicable

@amitchandak Can you explain with an example pls?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors