Forum Discussion

DomBateman's avatar
DomBateman
Frequent Visitor
1 year ago

USERELATIONSHIP only returning a value where inactive column date = active column date

Hi there,

 

I was hoping someone could help me understand why USERELATIONSHIP is filtering out all records except where the inactive column = active column in the below example. 

 

I have 2 tables: Record, and DimDate:

 

The active relationship is between DimDate[Date] and Records[Date_Completed]
There is an inactive relationship between DimDate[Date] and Records[Date_Conducted]

I have a measure that seems to only return a value where records[Date_Completed] = records[Date_Conducted]. I was expecting it to just calculate the average as if DimDate[Date] * Records[Date_Conducted] was the active relationship but this is not happening.

 

Overall Average =
CALCULATE(
AVERAGE(Records[Score]),
USERELATIONSHIP(Records[Date_Conducted],DimDate[Date]),
FILTER(Records, Records[RecordType] = 1)
)

 

The problem occured after I added USERELATIONSHIP. 

 

The measure is being used in a barchart, along with Records[Property_Name], which is itself filtered by a DimDate slicer. 

 

Just wondering if anyone can help me understand what im missing? The data types and date formats appear to match up which is what another post suggested might be the issue. 

Need Help

3 Replies

  • speedramps's avatar
    speedramps
    Super User
    1 year ago

    Download this PBIX example of data base "role play" relationships from OneDrive.

    Look at Page1 and Page2, the rleationshios and the DAX measures.

    Click here 

     

    We want to help you but your description is too vague. Please write it again clearly.

    Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want to do. That is a bit crazy. 😀

    Please just give a simple non technical functional description of what you want, then let us suggest the solution. Thank you.

    Provide example input data as table text (not a screen print) so we can import the data to build a solution for you.

    Remove any unneeded columns which may cause confusion.

    Rename columns to user friendly names. Avoid jargon.
    Also provide the example desired output, with a clear step-by-step description of calculations the process flow.
    Remember not to share private data ... we don't want you to get into trouble. 😧
    Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
    Try keep it simple and ask one question per ticket.
    You will get a quick response if you put time, care and effort into writing clear problem descriptions.
    Remember you are gertting free expert help, so please put lots of proper effort to asking questions and providing examples. 😀

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi DomBateman 

     

    I wish you all the best. I would like to confirm whether you have successfully resolved this issue or if you need further assistance. If speedramps 's reply was helpful to you, could you consider marking it as a solution? This will help more users who are facing the same or similar difficulties. Thank you!

     

    If you need further assistance, could you please provide your PBIX file and your expected results so that we can better assist you? How to provide sample data in the Power BI Forum - Microsoft Fabric Community Please remove any sensitive data in advance.

     

    Best Regards,
    Yulia Xu