How CALCULATE function works to pass filter from many side of relationship to one side

[shraddhab]

Hi everyone,

Can someone please explain the reason behind why can calculate force relationship to filter from many side to one side of relationship. I can't get my head around the reason behind how it works.

Example:
Customer City Count := DistinctCount(Customer[CustomerCity])
Purchasing Customer City Count := Calculate([Customer City Count], Sales)

Detailed step by step explanation of why it works would be of great help.

Thank you.

[v-lionel-msft]

Hi @shraddhab ,

Please refer to Introduction to Filter Context in Power BI .

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@shraddhab , basically, when use filter in calculate,what actually it returns, It returns a table, this means we can give a table in option where we can give filter. How table will behave depend on relation of table with measure. In you case it should give CustomerCity that has record in sales. ( As per theory , but you can check)

[amitchandak]

@shraddhab , if is join bidirectional, if yes you can. What are you try here a measure or a new column

//Try to check any value is not blank

Purchasing Customer City Count := Calculate([Customer City Count], not(isblank([Sales[sales])))

If you are looking for column, check my video

https://www.youtube.com/watch?v=czNHt7UXIe8

[shraddhab]

Hi @amitchandak , I am creating measure only and my formula posted does work. But I wanted to understand the reason behind why and how does it work conceptually?