List all rows of selected start or end date

[Anonymous]

Hello peers,

I have been working with Power BI filters for a while now but cannot wrap my head around the following:

I have a date table "Dates" (also marked as date table in Power BI) that has inactive relationships to date columns of other tables.  The table "Contracts"specifies contracts with customers that have a specific start and end date. I would like to list all contracts that have an start or end date that is selected in the filter of the date table.

So far I have tried to use CALCULATE or CALCULATE TABLE, like so:

CALCULATETABLE(Contracts, USERELATIONSHIP(Dates[Date], Contracts[EndDate]))

I have also tried:

CALCULATETABLE(Contracts, Dates[Date], USERELATIONSHIP(Dates[Date], Contracts[EndDate]))

And in an other try:

CALCULATETABLE(Dates, ALLSELECTED(Dates[Date]))

All three tables give me the entire range of dates from the date table, so not limited to the selection of the filter.

I will try to describe how I think my post differs from existing posts: The user has the possibility to selected multiple months/years (so using SELECTEDVALUE is not possible I guess). The selected dates are not necessarily in a consecutive  range, i.e. they can be "january - march - april",  so missing february. That is why I cannot use e.g. MAX and filter on that (this worked previously). Lastly, I do not want to calculate anything over the rows in the contract (e.g. AVERAGE) but I would really like to list the rows as-is.

I feel I am missing something fundamental of the CALCULATE(TABLE) function(s). But before I had no troubles with it...

Any help is much appreciated!

[Anonymous]

Thanks for all the helpful replies so far.

The desired output sample is attached in the image below:

If anything is unclear, pleaes let me know so I can clarify further.

[Icey · Accepted solution]

Hi @Anonymous ,

Try to create a measure like below and set it as a visual filter:

Measure 2 = 
VAR SelectedDates =
    VALUES ( Dates[Date] )
RETURN
    IF ( MAX ( Contracts[EndDate] ) IN SelectedDates, 1 )

Best Regards,

Icey

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

[Icey]

Hi @Anonymous ,

Please check if this is what you want:

Measure =
CONCATENATEX (
    CALCULATETABLE (
        Contracts,
        USERELATIONSHIP ( Contracts[EndDate], Dates[Date] )
    ),
    [contracts],
    ", "
)

Best Regards,

Icey

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

[Anonymous]

Hi Icey,

Thanks a lot for your reply.

I am afraid this does not work - I need the actual rows of the contracts table (not the strings), as I want to display the rows in a table visual. Would you happen to know how I do that?

Best,

[PC2790]

Basically what you need to do is pass on your filter's value in a variable and use it to show your the data in the end.

Please refer this video if it helps.

You can also fine something useful here- https://www.sqlbi.com/articles/using-the-selectedvalue-function-in-dax/

[Anonymous]

Hi PC2790,

Thanks a lot for you reply. I have watched the video and read the blogpost.

Unfortunately I have not reached my goal:

- I believe the blog post only refers to selected value. In my case, I need multiple selected dates as "foreign key" (or in the filter).

- Regarding the video: I tried using a variable that calculates ALLSELECTED(Dates[Date]), and filter using CONTAINSROW or IN on that variable. I get the following result:

That is, I get an empty measure, although BankAccountId is nonblank in all rows.

Weirdly, when I use a calculation on the dates table itself, e.g. DISTINCTCOUNT(Dates[Date]), it does give me the correct number.

When I try

Measure =

VAR SelectedValues = ALLSELECTED(Dates[Date]),

RETURN CALCULATETABLE(

SalesContracts, FILTER(SalesContracts, SalesContracts[EndDate] IN SelectedValues))

I get a copy of the SalesContracts table, unfiltered. This also happens when I use e.g. USERELATIONSHIP as extra filter (having an inactive relation ship specified). 

Intuitively it seems that ALLSELECTED does not 'work' outside the Dates table. Do you perhaps know what I am missing here?

Thanks again!

Best,

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format?

refer to these two blogs

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

Hi amitchandak,

Thanks a lot for your reply.

Referring to your blog posts: these are indeed very helpful, as a temporary solution I have already added a row for every day between the start and end date of a contract. This method was already in place and works fine, but is more difficult to maintain.

Moreover, I ultimately  need one date "axis" or "main table" to filter multiple tables. So whether I have a base "date column" in the contracts table and use this column to filter all other tables, or I have one "date table" which I use to filter all tables including the contracts table, the problem remains the same.

I will add the desired sample output in my initial post.

Thanks again for your reply!