Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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,
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 Sounds really good. Can you please elaborate more with an example?
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
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,
Really appreciate your support!
@Anonymous If you are in PBI and connected to SSAS then try DETAILROWS property of a measure: https://dax.guide/detailrows/
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |