Date Slicer with multiple columns

[GAURAVG]

Hello all,

I have a table "Table1", which has few requirements and 3 columns having the dates in which they were in various states(draft,inProgress,closed).See table below.

I then have 3 visuals date wise, for each state. 1 for draft, 1 for InProgress,1 for Closed.

Now i want a single relative date slicer, which if i say filter for last 3 months, applies to all visuals.

Currenty if i filter Closed last 3 months,in a date slicer, for the draft visual, it also shows R4, which i dont want.Iwant it to apply last 3 months for all visuals.

Any idea on this?

Requirement	Draft	InProgress	Closed
R1	10-May-21	20-Jun-21	10-Jul-21
R2	10-May-21	20-Jun-21	15-Jul-21
R3	15-May-21	05-Jul-21	10-Aug-21
R4	22-Jul-21	10-Aug-21	03-Oct-21
R5	28-Aug-21	30-Aug-21	10-Oct-21

[v-janeyg-msft]

Hi, @GAURAVG 

If you want to use date slicer, the context will change, and you won't be able to filter multiple columns of data in one step.

There are a simple workaround, but you need to use the measure to all visuals' filter pane.

Create a single date table, and create e measure to filtere three date columns in filter pane.

Like this:

Table 2 = CALENDARAUTO()

Measure =
IF (
    MAX ( 'Table'[Draft] )
        IN DISTINCT ( 'Table'[Draft] )
            && MAX ( 'Table'[InProgress] )
                IN DISTINCT ( 'Table 2'[Date] )
                    && MAX ( 'Table'[Closed] ) IN DISTINCT ( 'Table 2'[Date] ),
    1,
    0
)

If the relative date is fixed, you can use the calculated column to filter in all visuals.

Like this:

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[GAURAVG]

Thank you for the reply. My relative date is not fixed.

But if you see from your calculated column example, after i filer for "Last 3 months"R4 is also considered for Draft and In Progress visuals..But for R4 Draft was way back in Aug(08), and hence in the visual, it should not show up.

Also for the measure,I would however have to create arelationship between the Table2 and table 1.Else i wont be able to apply filter on visuals.Isnt that correct?

[v-janeyg-msft]

Hi, @GAURAVG 

If you download  and check my sample, you won't say that the result is incorrect... The data you provided didn’t match the results for the last 3 months, so I changed the date.

And the easiest way to use measure I have given. Other methods, such as what amit said, are not applicable to all visuals at once. It also needs to be customized according to your needs. If you want to use his method, I suggest you check the documentation carefully.

USERELATIONSHIP function (DAX) - DAX | Microsoft Docs

HR Analytics - Active Employee, Hire and Terminati... - Microsoft Power BI Community

Best Regards,
Community Support Team _ Janey

[amitchandak]

@GAURAVG , You need to create a date table and join all dates with that. That will create one active and other inactive joins

You can activate the join userealtionship

Refer example

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[GAURAVG]

Hello,

I have tried creating a CalendarAuto table and then creating a relationship between [Table1][Draft] and Calendar[Date]. But, i can only create a One-to-Many relationship.It should ideally be a Many-One relationship. If i change cardinality, i get the error as "The cardinality you selected isnt valid". I am guessing this occurs, as i have more than 1 req with same Draft date

[amitchandak]

@GAURAVG , Create date table using calendar , based these take some Min start and Max year end date and try

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[GAURAVG]

I tried that too, but i get same cardinality..One-Many