Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
ok, so I got two dates: 1. Purchase Date 2. Sell Date these two columns come from Table1 and then I have 1 main table for date.
I need to add a filter so I can filter one date and see all products purchased before this date and all products sold after that date. i tried to make a relationship but the relation can not be built because of the two dates Purchase date and Sell date these two columns can not be related to one date.
Solved! Go to Solution.
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
One of many ways to solve this is to have one active relationship and one inactive relationship.
count by purchase date / count by sell date
Order count by purchase date: =
COUNTROWS(order_fact)
USERELATIONSHIP function (DAX) - DAX | Microsoft Learn
Order count by sell date: =
CALCULATE (
COUNTROWS ( order_fact ),
USERELATIONSHIP ( 'Calendar'[Date], order_fact[sell_date] )
)
However, if counting of the progress is needed, slightly different approach has to be considered.
Order count in progress: =
CALCULATE (
COUNTROWS ( order_fact ),
REMOVEFILTERS ( 'Calendar' ),
order_fact[purchase_date] <= MAX ( 'Calendar'[Date] ),
order_fact[sell_date] >= MIN ( 'Calendar'[Date] )
)
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
One of many ways to solve this is to have one active relationship and one inactive relationship.
count by purchase date / count by sell date
Order count by purchase date: =
COUNTROWS(order_fact)
USERELATIONSHIP function (DAX) - DAX | Microsoft Learn
Order count by sell date: =
CALCULATE (
COUNTROWS ( order_fact ),
USERELATIONSHIP ( 'Calendar'[Date], order_fact[sell_date] )
)
However, if counting of the progress is needed, slightly different approach has to be considered.
Order count in progress: =
CALCULATE (
COUNTROWS ( order_fact ),
REMOVEFILTERS ( 'Calendar' ),
order_fact[purchase_date] <= MAX ( 'Calendar'[Date] ),
order_fact[sell_date] >= MIN ( 'Calendar'[Date] )
)
User | Count |
---|---|
12 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
24 | |
19 | |
14 | |
10 | |
7 |