cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
emmaanne93
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
Super User III
Super User III

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


Regards

Miguel Félix


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

Proud to be a Datanaut!

Check out my blog: Power BI em Português


Super User IV
Super User IV

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/
Highlighted
emmaanne93
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

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

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 III
Super User III

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

 

 

 


Regards

Miguel Félix


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

Proud to be a Datanaut!

Check out my blog: Power BI em Português


Super User IV
Super User IV

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors