none
Only show rows where one measure is populated RRS feed

  • Question

  • Hi All,

       Something that I think can be done in Power BI but wanting to see if I can apply within SSAS. I have a filtered measure (i.e. if measure1 is below a threshold, measure2 is blank) which, on its own, correctly shows only those where the threshold is met. However, if I add another measure (measure3) to my report (currently Excel), all the rows where measure2 is blank are shown, is there a way to maintain display of only those rows where we have a result for measure2?

    Thanks

    Tuesday, August 28, 2018 2:51 AM

Answers

  • On SSAS-side one of possible options is creating additional measure4 having null if measure2 is null and equal to measure3 otherwise (approach has limitations if user decides to change measure3 to measure6 or say measure9)
    - other alternative is modifying measure3 but it can break existing reports (plus same possible complications as above),
    - third base scenario is having additional attribute and assign modified measure3 on one of non-default elements

    Same approach (calculated measures via M-Code/DAX/MDX) can be implemented on PowerBI side if you're using import into your PBI data model, unfortunately in case of LiveConnection there's no control over custom calculations,
    BUT (!!!) you can apply visual level filters with option to show items when the value: "is not blank" - this option if applied on measure2 - also filters measure3 and solves your problem.
    • Marked as answer by RyanAB Tuesday, August 28, 2018 11:23 PM
    Tuesday, August 28, 2018 4:24 AM
  • This is an easy client-side filter in Excel (in Power BI, this is also client-side filtering).

    Or you can use scoped assignment in SSAS MD:

    scope measures.allmembers; --or any set of measures you need thus filtered
    this = iif(isempty(measures.measure1), null, measures.currentmember);
    end scope;



    Expect me to help you solve your problems, not to solve your problems for you.

    • Marked as answer by RyanAB Tuesday, August 28, 2018 11:23 PM
    Tuesday, August 28, 2018 7:18 AM

All replies

  • On SSAS-side one of possible options is creating additional measure4 having null if measure2 is null and equal to measure3 otherwise (approach has limitations if user decides to change measure3 to measure6 or say measure9)
    - other alternative is modifying measure3 but it can break existing reports (plus same possible complications as above),
    - third base scenario is having additional attribute and assign modified measure3 on one of non-default elements

    Same approach (calculated measures via M-Code/DAX/MDX) can be implemented on PowerBI side if you're using import into your PBI data model, unfortunately in case of LiveConnection there's no control over custom calculations,
    BUT (!!!) you can apply visual level filters with option to show items when the value: "is not blank" - this option if applied on measure2 - also filters measure3 and solves your problem.
    • Marked as answer by RyanAB Tuesday, August 28, 2018 11:23 PM
    Tuesday, August 28, 2018 4:24 AM
  • This is an easy client-side filter in Excel (in Power BI, this is also client-side filtering).

    Or you can use scoped assignment in SSAS MD:

    scope measures.allmembers; --or any set of measures you need thus filtered
    this = iif(isempty(measures.measure1), null, measures.currentmember);
    end scope;



    Expect me to help you solve your problems, not to solve your problems for you.

    • Marked as answer by RyanAB Tuesday, August 28, 2018 11:23 PM
    Tuesday, August 28, 2018 7:18 AM
  • Thanks both. As there are a multitude of measures that would be used in conjunction with Measure2, I was seeing if there was a way to avoid creating related measures. Since it is so much easier to do on the client-side, I think that may be the better option.
    Tuesday, August 28, 2018 11:23 PM