How to show to 2 different date columns in the same visual and still use the filters

[Calouro]

Hello,

I have a table called "Use cases" and I Have 2 different dates inside, "PoC created date" and "PoC started date".

"PoC created date" is never empty and "PoC started date" has blank values.

What I'm trying to achieve is, to show them both in the same clustered column chart. I was able to achieve this by creating a date column in 2 different tables (1 for each) and then merging them into 1 table called "Merge1".

But the relationship between "Merge1" and the original table "Use cases", where I have all my other fields, is not working properly, and when I filter the page, the chart gives me the wrong figures. I tried different relations between "Merge1" and "Use cases" without success.

Can anyone help?

Thank you in advance,

[v-heq-msft · Accepted solution]

Hi @Calouro ,

Here some steps that I want to share, you can check them if they suitable for your requirement.

Here is my test data:

1.Create a separate calendar table that includes all the dates that could appear in either "PoC created date" or "PoC start date".

Table =
CALENDAR (
    MINX ( 'User cases', [PoC created date] ),
    MAXX ( 'User cases', [PoC start date] )
)

2.Create a relationship between the calendar table and the "PoC created date"and"PoC start date" in "Use cases" table.

Ensure that both relationships are inactive to begin with.

3.Use USERELATIONSHIP in Measures

PoC Created Count =
CALCULATE (
    COUNTROWS ( 'User cases' ),
    USERELATIONSHIP ( 'Table'[Date], 'User cases'[PoC created date] )
)

PoC Start Count =
CALCULATE (
    COUNTROWS ( 'User cases' ),
    FILTER ( 'User cases', NOT ( ISBLANK ( 'User cases'[PoC start date] ) ) ),
    USERELATIONSHIP ( 'Table'[Date], 'User cases'[PoC start date] )
)

 4.Final output

Best Regards,

Albert He

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

[aj1973]

Hi @Calouro 

In my understanding you want to calaculate number of PoC by Date (Cerated and Start) !

If so you add a Date table to your model and double join it to your other table by Created date and start date (One active relationship and one inactive). COUNTROWS  by date then at the visual you put Date from Date Table and you add your 2 measures.

[Calouro]

Hi aj1973,

yes, this was exactly what I did and it works, but I want something more: the option to relate this new date table to the original table and filter in both ways.

When I did this, the relation never worked properly and I could not filter the tables (using fields from the original table).

[aj1973]

Didn't understand!

Can you share a sample or screenshot of your model