Forum Discussion
Post PartisanMultiple USERELATIONSHIPS functions within an AND and within a CALCULATE
D_PBI - You need to take the USERELATIONSHIP filters out of the FILTER statement, and add them as additional filters.
Also, I would not recomment using FILTER( 'Table', 'column' ) as you have, this is a classic anti pattern and will result in a slower calculation, you should filter a column rather than a table. Here is a good article for this: https://www.sqlbi.com/articles/filter-columns-not-tables-in-dax/
One final thing, I would recommend simplfying your final condition to use NOT IN {"Value1", "Value2" } etc.
I hope this helps, if you need more please provide the DAX code outside of a screenshot and some sample data.
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
Hi D_PBI,
I wanted to check if you had the opportunity to follow up on our previous conversation. If yes can you please provide the sample data so that we can provide you with the accurate and correct solution.
Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so that other community members can find it easily.
Thank you.
Super UserPerhaps you do not need the use the relationships
Simply create temporary subset with just the rows you need.
Then count the agreements in that temporary subset
Like this
Answer =
var mydare = TODAY() // you can repace this with your variable
var mysubset =
FILTER( yourtable,
(youttable[terminationdate] > mydate &&
youttable[expiryndate] > mydate ) ||
youttable[expiryndate] not in {"Cancelled", "Died", "Rejected"}
)
RETURN
CALCULATE(
DISTINCOUNT(yourdate[addrmentID),
mysubset
)
You can learn about role based date relationships here (click to download form Onedrive)
Role based date relationships.pbix
Look at all the pages, the relationship structure and dax measure to see how it works.
I have added comments to help you learn
Please click thumbs up because I have tried a lot to help.
Then click accept solution if it works.
Many thanks