locked
Tabular model: dimension not related to measure RRS feed

  • Question

  • I'm stumped for a label for what I need.

    I have a tabular model with a user dimension and a label dimension and a measure.  The measure is not related to label dimension at all.  So what I get in the pivot table that I build 

    Label1 Label2
    User1 20 20
    User2 10 10
    User3 30 30

    The result for each column is the measure result for the user.  So if I have 100 labels, measure value is 20 for User1 and 10 for User2 and 30 for User3.

    How do I fix this?  I want it to be obvious that label is not relevant to measure. Basically I want my model to clearly indicate that certain measures are related to certain dimensions and not to others.


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    Tuesday, July 2, 2019 7:05 PM

Answers

  • So your tabular model implements a non-trivial data model in which some measures are unrelated to some dimensions. This can be perfectly normal and require no fixing at all, depending on the model. As to the user experience with this model, unfortunately you'll have to document these details.

    A possible workaround might be to enhance each measure with checking whether it's being calculated for specific members of unrelated dimension and not for their totals, and for such cases return some warning instead of the (duplicated) value. 


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

    • Marked as answer by Russ Loski Friday, July 5, 2019 10:40 AM
    Wednesday, July 3, 2019 12:26 PM
  • Perspectives work great for segmenting complex data models, provided this is logically possible.

    Consider a sales+stocks data model, where both sales and stocks facts need to be available simultaneously for stocking analysis. If there happened to be e.g. 'sales channel' dimension, it would have no relation to purely stocks-based measures, and perspectives won't help with that.


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

    • Marked as answer by Russ Loski Friday, July 5, 2019 10:40 AM
    Wednesday, July 3, 2019 1:11 PM
  • Hi Russ,

    Thanks for your question.

    Per you description, you haven't built relationship between label dimension table and fact table where that measure you're referencing. If you could built that relationship between the two tables, the appearance would be fixed.

    For more details, please refer:

    Relationships

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Russ Loski Friday, July 5, 2019 10:41 AM
    Wednesday, July 3, 2019 2:29 AM
  • Yes, this is the idea. It could well be a pain to implement though, because you'd need to individually alter each qualifying measure, code each measure to check for each unrelated dimension and return values of different data types, and detecting filters in DAX isn't exactly straightforward either.

    So if you make this model available to end-users for creating reports, my first idea would be to provide detailed documentation explaining how dimensions and measures interact.


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

    Wednesday, July 3, 2019 9:05 PM

All replies

  • Hi Russ,

    Thanks for your question.

    Per you description, you haven't built relationship between label dimension table and fact table where that measure you're referencing. If you could built that relationship between the two tables, the appearance would be fixed.

    For more details, please refer:

    Relationships

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Russ Loski Friday, July 5, 2019 10:41 AM
    Wednesday, July 3, 2019 2:29 AM
  • The problem is that there is no logical relationship between label and measure.  What I want is a warning sign that label and measure don't go together.

    One idea I had was to create a perspective that included user and measure but did not include label.  Label would be part of a different perspective that includes a different measure (and other dimensions).  Does this sound like a good plan?


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    Wednesday, July 3, 2019 11:56 AM
  • So your tabular model implements a non-trivial data model in which some measures are unrelated to some dimensions. This can be perfectly normal and require no fixing at all, depending on the model. As to the user experience with this model, unfortunately you'll have to document these details.

    A possible workaround might be to enhance each measure with checking whether it's being calculated for specific members of unrelated dimension and not for their totals, and for such cases return some warning instead of the (duplicated) value. 


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

    • Marked as answer by Russ Loski Friday, July 5, 2019 10:40 AM
    Wednesday, July 3, 2019 12:26 PM
  • Perspectives work great for segmenting complex data models, provided this is logically possible.

    Consider a sales+stocks data model, where both sales and stocks facts need to be available simultaneously for stocking analysis. If there happened to be e.g. 'sales channel' dimension, it would have no relation to purely stocks-based measures, and perspectives won't help with that.


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

    • Marked as answer by Russ Loski Friday, July 5, 2019 10:40 AM
    Wednesday, July 3, 2019 1:11 PM
  • A possible workaround might be to enhance each measure with checking whether it's being calculated for specific members of unrelated dimension and not for their totals, and for such cases return some warning instead of the (duplicated) value. 


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

    If I understand correctly, with this workaround, I would return some kind of warning message in place of the measure value:

    "Not joined" (or something more informative) rather than 20 in the above results




    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com


    • Edited by Russ Loski Wednesday, July 3, 2019 6:48 PM
    Wednesday, July 3, 2019 6:48 PM
  • Yes, this is the idea. It could well be a pain to implement though, because you'd need to individually alter each qualifying measure, code each measure to check for each unrelated dimension and return values of different data types, and detecting filters in DAX isn't exactly straightforward either.

    So if you make this model available to end-users for creating reports, my first idea would be to provide detailed documentation explaining how dimensions and measures interact.


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

    Wednesday, July 3, 2019 9:05 PM