none
DAX: Userelationship() problems

    Question

  • Hi all,

    Well, I have a complex question, I hope you can help

    I have a unactive relation and I´d like to retrieve a column to use it into my calculations.

    As I don´t want to sum(), I made min(), but it isn´t working properly, as it returns the MIN() value for all rows. MAX() does the opposite.

    see example (operator rank is int type field)

    =CALCULATE(MIN('machine center'[operator rank]);USERELATIONSHIP('calendar entry'[No_];'machine center'[TableID]))

    this returns me 0 if I do min(), and 1 if I do max().

    any ideas?

    thanks!


    • Edited by the_txeriff Tuesday, February 19, 2013 12:47 PM
    Tuesday, February 19, 2013 12:45 PM

Answers

  • USERELATIONSHIP expects the columns that an existing relationship is defined on.  Also, even though documentation says the engine will flip these if you enter them in the wrong order, technically you are supposed to pass the many side first, then the one side.

    Seems strange that you are defining the join to the Calendar table via a "TableID" from the MachineCenter table.  I would have expected a date column (or whatever the grain of the Calendar table is).

    Let me know if that helps.  Would also help if you posted a screen shot of your existing model, as well as your expected result.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com



    Tuesday, February 19, 2013 3:05 PM
    Answerer

All replies

  • USERELATIONSHIP expects the columns that an existing relationship is defined on.  Also, even though documentation says the engine will flip these if you enter them in the wrong order, technically you are supposed to pass the many side first, then the one side.

    Seems strange that you are defining the join to the Calendar table via a "TableID" from the MachineCenter table.  I would have expected a date column (or whatever the grain of the Calendar table is).

    Let me know if that helps.  Would also help if you posted a screen shot of your existing model, as well as your expected result.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com



    Tuesday, February 19, 2013 3:05 PM
    Answerer
  • HI Brent,

    The calendar entry table it´s not a date table. I finally wrote down in a piece of paper the tables and relations and decided to do it in another way. I realized that relations where wrong done and also decided to "mark" those fields in the ETL process than using that complex DAX that didnt work.

    thanks anyway.

    EDIT:

    go to previous post

    • Edited by the_txeriff Wednesday, March 06, 2013 3:37 PM
    Tuesday, February 26, 2013 10:04 AM
  • HI brent,


    It´s me again.


    I finally decided to go by sql update and mark those rows using int type field.


    the DAX I was trying to , its like this (i translated into english):

    testmeasure:=CALCULATE(MAX(ProdOrder[OPType]);USERELATIONSHIP(CapacityLedger[Prod_ Order No_];ProdOrder[Order No_]))


    So we´ve got "production order" dimension and "capacityledger" fact table. The relationship between both is created but not enabled.


    As I´m not doing sum ([OPTYPE] its an int type field) I must use a MAX or MIN but it doesnt return proper value. It returns 1 if using MAX() or 0 if using MIN().

    Is it possible to do this DAX query?

    thanks!




    • Edited by the_txeriff Wednesday, March 06, 2013 3:38 PM
    Wednesday, March 06, 2013 3:34 PM
  • Is this still an issue?

    Thanks!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Tuesday, September 17, 2013 6:35 AM
    Owner