cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
emmaanne93 Frequent Visitor
Frequent Visitor

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

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!

7 REPLIES 7
Highlighted
Super User
Super User

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

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

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

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
emmaanne93 Frequent Visitor
Frequent Visitor

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

@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.

 

ThanksCapture1.JPGCapture2.JPGCapture.JPG

 

emmaanne93 Frequent Visitor
Frequent Visitor

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

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.

 Capture3.JPG

emmaanne93 Frequent Visitor
Frequent Visitor

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

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.

 Capture3.JPG

Super User
Super User

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

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.

 

proje.png

 

Regards,

MFelix

 

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

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

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 135 members 1,825 guests
Please welcome our newest community members: