Using a sliced measure from one table in a calculated column in another table?

[emmaanne93]

Hi there,

In table B I have a series of rows with start dates and end dates. I created a calendar table based on the min and max of these start and end dates, and then a slicer to select a range of dates I am interested in. I created two measures in the calendar table, called First date and Last date, which are the sliced time frame that I am interested in looking at.

In table B, I would like a calculated column that says something like "if(and('table B'[start date]< 'calendar table'[Last date],'table B'[end date]>'calendar table'[first date]),1,0)"

From here I can filter my table to only return rows where the time frame in table B falls within the range I have sliced in my calendar table. The issue I am running into is, the calculated column in table B that references the 'calendar table'[first date] and 'calendar table'[last date] are ignoring the date slicer, and instead just choosing the first and last date of the entire date range.

Is there a way to use a sliced measure in a calculated column of another table? Or is there any other more efficient way to do this?

Thanks in advance!

[Ashish_Mathur]

Hi,

Please describe your business question, show your data tables and also your expected result.

[emmaanne93]

@Ashish_Mathur and @MFelix, thanks for your responses! I have a table with several columns including a start date column and an end date column. I want to be able to filter the table dynamically based on a user input (i.e. the date slicer in the bottom left corner). This date slicer is connected to the calendar table 'Date'. I want my 'linesout' table to return the rows that overlap the user defined range in any capacity (i.e. the end date > user defined start date and the start date < user defined end date). It sounds like I need to create an inactive relationship between the 'date' table and the [start] and [end] columns of my 'linesout' table and then use the "userelationship" function, but I am not sure how to do that.

Thanks

[emmaanne93]

Hey y'all, I was able to achieve what I want by just creating two separate slicers for the end date and start date and foregoing the calendar table entirely. The only limitation here is that the graphics aren't great (see photo) because I want it to look like a simple range of time, not two separate ranges. Also, I haven't figured out how to force the end date slicer to be greater than the start date slicer.

 

[emmaanne93]

Hey y'all, I was able to achieve what I want by just creating two separate slicers for the end date and start date and foregoing the calendar table entirely. The only limitation here is that the graphics aren't great (see photo) because I want it to look like a simple range of time, not two separate ranges. Also, I haven't figured out how to force the end date slicer to be greater than the start date slicer.

 

[Ashish_Mathur]

Hi,

I would convert the two date columns into a single one in the Query Editor and then build only a date slicer.

[MFelix]

Hi @emmaanne93,

I would create the dimension date table and make the relationship inactive between start date / end date and the calendar table.

Then make the following measure:

Valid =
VAR MinDate =
    MIN ( DimDate[Date] )
VAR MaxDate =
    MAX ( DimDate[Date] )
RETURN
    CALCULATE (
        COUNT ( Projects[Project] );
        FILTER (
            Projects;
            Projects[Start Date] >= Projects[Start Date]
                && Projects[End Date] <= MaxDate
        )
    )

Then you can use this measure on your tables and visuals to filter out the results if you add it to your visual level filters or just by adding them to your charts.

If you need to calculate the count based on Start and End date you can use the following measures:

Projects Start Date =
CALCULATE (
    COUNT ( Projects[Project] );
    USERELATIONSHIP ( DimDate[Date]; Projects[Start Date] )
)

Projects End Date =
CALCULATE (
    COUNT ( Projects[Project] );
    USERELATIONSHIP ( DimDate[Date]; Projects[End Date] )
)

Total Value by End Date = 
CALCULATE (
    SUM(  Projects[Value] );
    USERELATIONSHIP ( DimDate[Date]; Projects[End Date] )
)

See result below and attach PBIX file, the slicer is based on the DimDate Table.

Regards,

MFelix

[MFelix]

Hi @emmaanne93,

You cannot filter a column based on a measure because measures are calculated based on context that can be several levels of detail or aggregation and columns are on at row level aggregation so when you get the measure on a column you will get the full content of the table.

Don't know what type of calculations you want to do and how but you must do a relationship between the calendar table and the two columns you have of start and end, be aware that you won't be abble to have both relationship active so you can have one active and the other inactive or both inactive, then make measures using the USERRELATIONSHIP formula.

If you give an example of what you want to achieve I can try and show you how it would work on your data.

Regards,

MFelix