Dax Measure with specific date column from another table for Count

[dbrandone]

I work in the medical/health field and have multiple tables that are linked together. Each table specifies different parts of our process and are connected together in a star/snowflake pattern(half and half). The date table is connected centrally and is functioning for time intelligence throughout the schema. Almost every table in the dataset has a date/time column specifiying a specific part of the process(Referral Time, Preservation Time, Surgery Time, Injury Time and so on. 

The issue is that I need to write a calculate measure where the time intelligence portion of the measure uses a specific date/time column in a specific table. That date/time column is not in the same column as the count, and filter sections of the calculate functions, but the two tables are connected together with relationships. 

How would I write the measure to allow for the calculations and visuals to show based on the specific date/time? Would this be done in the calculation somehow or maybe in the visual filter?

I have to use "Week Ending" in my visual and I have that column in my date table. I am not sure how I combine week ending from the date table, but used a seperate date/time in the calculate/count measure.

Any help is appreciated

[parry2k · Accepted solution]

@dbrandone yes, if you have 2nd relationship between v_dbo_referral and date table (you already have one active relationship) and the 2nd one will be inactive which is fine, and that's where USERELATIOBSHIP comes in the picture, and your measure will look like this:

New Measure = 
CALCUALTE ( COUNTROWS ( YourTable ), USERELATIONSHIP ( YourTable[InactiveDateColumn], DateTable[Date] ) )

and to visualize the data, you will use the column from the date table and above measure and it will use the inactive relationship to show the data.

I hope it helps. If not, then I will recommend creating dummy data and share the pbix file.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@dbrandone It is possible, seems like you need to use USERELATIONSHIP DAX function to activate the inactive relationship 

Maybe share a sample pbix file using one drive/google drive and remove sensitive information before sharing and I can look into providing the solution. Make sure to share the expected output of what you are looking for.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[dbrandone]

Hi @parry2k,

I appreciate you reaching out. Unfortunately, our records are tied into medical records so I couldn't share any of it. What I can share is a screenshot of my table relationships. The date/time that I am needing to use is in the "v_dbo_referral" table and is not connected to the Date table as another date/time column in the referral table is connected to the the date table. The count measure that I need to use has all of the rest of the calculation componenets in the "v_EyeRecovery_EyeTis..."(right in the middle of the screen". The two tables are connected. 

When you say "UserRelationship", do you mean connecting the tables via relationship, but since there is already a connection, the relationship will be inactive, but I could use the UserRelationship to draw off of that inactive relationship? I was just reading about it, but still do not completely visualize how to perform this. Let me know if I can help with more information.