Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How do I pass a filtered (by slicer) "min" measure from one table 1 to table 2 ?

Hi all,

 

I am creating a dashboard that relies on 2 slicers- Date and Product.

 

There is a table ("T1") that has columns - date, product and CO2. I created a measure to get the min value of the date and CO2. These measures are dynamic based on the slicer.

 

I want to pass these dynamic values to another table ("T2"). How do I constantly pass these dynamic values to T2 as the slicer changes? I need these values to be always changing so I can do calculations based on it.

 

How do I go about doing this?

 

 

9 REPLIES 9
AllisonKennedy
Super User
Super User

You can only pass slicer selections and resulting measure values to another measure, not a DAX table or column.
https://excelwithallison.blogspot.com/2020/09/reporting-order-of-operations.html

There are ways to create virtual tables and columns within your measure, and then you can plot it in a table visualization in Power BI report.

If you need further help, please provide sample input data we can copy/paste or a sample pbix file via OneDrive, along with drawing or picture of your desired result.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

Screenshot (533).pngSlicerSlicer

 

Thank you for your response.

 

What I require is to get the Minimum Value based on the slicer's Date and Product to be dynamic in the column "Bin labels" and also the minimum date in the "Date" column base on the same slicer's setting. 

 

As is, it only gives the minimum value of the entire dataset but categorized by product.

 

Can you please suggest some DAX syntax to create such a virtual table? I am new to Power BI.

 

 

What does the other table look like and what relationship is between them? Please share the current DAX for Bin Labels column - we will need to convert that to a MEASURE instead of a column in order for it to update based on the slicer selection. 🙂

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

The Table headers to be considered are- Date, Product, and CG. (or CO2 mentioned above). This table would be identified as 'Brewery'.

 

I used 2 lookup tables - 'Product' and 'Calendar' to link tables 'Brewery' and 'New attempt'... all relationships are linked by product and date.

 

Screenshot (535).png

 

A measure was created 'CG Min' in the Brewery table which gets the minimum value based on a slicer. I have checked and it works properly.

 

The following is the syntax in the New Attempt table for column Bins Labels:

Bins Labels = Brewery[CG Min]+('New attempt'[Count]*[Bins])

 

Bins is a measure: Bins= (CG Min-CG Max)/20

 

I just require the min value in the Brewery table when sliced to be used in the column to perform the above calculation. These calculated values would be used further in another calculation in the same table. When these values are finally calculated, it will be plotted as a line graph.

 

Please assist.

 

Hi @Anonymous ,

Please try to update the formula of measure [Bins Labels] as below:

Bins Labels =
VAR _cgmin = Brewery[CG Min]
RETURN
    _cgmin + ( 'New attempt'[Count] * [Bins] )

If it still not working, please provide the current result and expected result with screenshots. And please give more details(sample data and calculation logic etc.) on it. Thank you.

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Your solution does not work but thanks for your help.

 

Unfortunately, I do not have the privilege to share the data or the PBI file.

However, I would take this opportunity to explain exactly what I am trying to accomplish.

I am attempting to create a Normal Distribution using a Line Chart for quality data. The main table has columns- Date, Product, and CG. CG is the quality data I am trying to investigate. 

 

The Excel Calculations are as follows:

-Screenshot (537).png

 

- Min and Max of CG data is based on Date and Product

- Bin Size= (Max-Min)/20

- Mean (X bar) and Standard Deviation is to be Calculated based on Date and Product

- The first value on the column 'Bins' is Min and then each consecutive value in the column is= previous value+ Bin Size

- Then the **bleep**% is calculated by (sub group aka # of data points)*Bin Size*NORMDIST(sub group,Mean, Standard Deviation)

Screenshot (538).png

 

The **bleep**% would be plotted.

 

PLEASE ASSIST.

 

@Anonymous

First, see if this custom visual can help: https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104380776?tab=Overview

It will do the distribution for you, so no need for custom calculations.

Otherwise, if you want to be able to adjust the min and max values by a slicer selection, you will need to do everything as a Measure. That makes getting the bin labels a bit challenging.

Can you either paste your data from the screenshot above into a post directly or upload the excel file to onedrive so we can use it for testing purposes please?

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

Hi Allison,

 

See attached for the data... I would like the output to be dynamic based on the product and date. 

 

I also attached the excel file so the calculations are clear (Cells I7 to P28). 

 

https://www.dropbox.com/sh/fshvbteu7sqijm3/AAAhbD5QkGJHvHkvqO8SnfwGa?dl=0

 

Please assist.

Anonymous
Not applicable

@AllisonKennedy @v-yiruan-msft  any solutions?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.