cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Return a value from another column based on the value of a measure

I have a measure that returns the cell count in blue. This value is equal to the width through the reference point for all cells that are equal to zero. The reference point is in red.

What I would like is a measure to return the values in green. The value of the left green cell is the beginning of the width, and the value of the right green cell is the end of the width.

Home: Measure1

End of medida2

eye_return_min_max_cdc.jpg

The row that contains the green values is CDC(Coarse+Fine).

This measure returns the width in blue:

Width

•IF (
HASONEVALUE ( raw_data[CDC (Coarse+Fine)] ),
SUM ( raw_data[Valor] ),


Return
COUNTROWS (
FILTER (
VALUES ( raw_data[CDC (Coarse+Fine)] ), raw_data[CDC (Thick+Fine)]
&& CALCULATE ( SUM ( raw_data[Value] ) ? 0)
))

I have a sample of the above data contained in the Excel file which also contains the width measurement!

https://drive.google.com/open?id=1Fr0dJCQY9kbwvWebMp_iFUtFO9w1ZhKz

I need help with this, please. Thank you.

1 ACCEPTED SOLUTION

Hi @roncruiser ,

 

Sorry for our delay in response, we can create following measuers to meet your requirement:

 

Home: Measure1 =
VAR TempTable =
    SUMMARIZE (
        'raw_data',
        [Freq],
        [CDC (Coarse+Fine)],
        [SN],
        [RD_WR],
        [Vendor],
        [Rank],
        [Vref],
        "Total", SUM ( 'raw_data'[Value] )
    )
VAR VerfOfMaxValue =
    MAXX ( TOPN ( 1, TempTable, [Total], DESC ), [Vref] )
RETURN
    MINX (
        FILTER ( TempTable, [Vref] = VerfOfMaxValue && [Total] = 0 ),
        [CDC (Coarse+Fine)]
    )

 

End of medida2 = [Home: Measure1] + [passingWidth]

 

3.jpg


Best regards,

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

View solution in original post

7 REPLIES 7
Community Support
Community Support

Hi @roncruiser ,

 

Sorry for that, but we cannot access the file you shared and Cannot understand your data model clearly, Could you please provide a mockup sample based on fake data or describle the fields of each tables and the relations between tables simply? It will be helpful if you can show us the exact expected result based on the tables. Please upload your files to OneDrive For Business and share the link here.

 

Please don't contain any Confidential Information or Real data in your reply.


Best regards,

 

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

Hi,

Thank you for the reply.

I've since updated the link and added another link to the folder containing the file.  Both links are working now.

 

https://drive.google.com/drive/folders/1rz8B_COx_boHI9k7Jhs8kq9R9zaBMJgw?usp=sharing

https://drive.google.com/open?id=1Fr0dJCQY9kbwvWebMp_iFUtFO9w1ZhKz

 

Please take a look.

 

I will update with more detail soon.  Thank you!

 

Updated the first post with further clarification.

 

Thanks!

Hi @roncruiser ,

 

Sorry for our delay in response, we can create following measuers to meet your requirement:

 

Home: Measure1 =
VAR TempTable =
    SUMMARIZE (
        'raw_data',
        [Freq],
        [CDC (Coarse+Fine)],
        [SN],
        [RD_WR],
        [Vendor],
        [Rank],
        [Vref],
        "Total", SUM ( 'raw_data'[Value] )
    )
VAR VerfOfMaxValue =
    MAXX ( TOPN ( 1, TempTable, [Total], DESC ), [Vref] )
RETURN
    MINX (
        FILTER ( TempTable, [Vref] = VerfOfMaxValue && [Total] = 0 ),
        [CDC (Coarse+Fine)]
    )

 

End of medida2 = [Home: Measure1] + [passingWidth]

 

3.jpg


Best regards,

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

View solution in original post

@v-lid-msft 

 

Thanks for the help!  I need to further understand what is happening in the DAX formula. 

Can you please help me break it down by each sub section of the DAX formula. 

In comments, I've added what I think is going on.  I've also asked a few questions along the way.

Correct me if I am wrong please.

-----------------------------------

Home: Measure1 =

 

//VAR TempTable returns a table of the sum of each cell based on the grouping selected. Is this correct?

//What is "Total"?
VAR TempTable =
    SUMMARIZE (
        'raw_data',
        [Freq],
        [CDC (Coarse+Fine)],
        [SN],
        [RD_WR],
        [Vendor],
        [Rank],
        [Vref],
        "Total", SUM ( 'raw_data'[Value] )
    )

 

//VAR VerfOfMaxValue orgranizes the values from TempTable by descending order.

// I am totally unsure what is happening here. The value returned is zero but the table is MAXX.

//What does [VREF] do specifically?
VAR VerfOfMaxValue =
    MAXX ( TOPN ( 1, TempTable, [Total], DESC ), [Vref] )

 

//Can't tell here until I verify how VerfOfMaxValue operates.
RETURN
    MINX (
        FILTER ( TempTable, [Vref] = VerfOfMaxValue && [Total] = 0 ),
        [CDC (Coarse+Fine)]

------------------------------------------------

Thank you very very much... help me to understand further please!
    )

Hi @roncruiser ,

 

Firstly, the TempTable is a summarize table, you can understand it as the table in "visual" page, it indeed calculated sum for different value in groupby column. Total is a new column, the value of total is sum of "value" column, actually it is like one cell in "visual" page.

 

Then we use TOPN ( 1, TempTable, [Total], DESC )  get the row which contain max value of total in TempTable (just like the red point in "visual" page), because it only have one row, so we can use max to get the current verf value of this row.

 

After we got the Verf of the row contain red point, we can just filter the current row and total (same as cell value in visual page) equal to zero, then the min value of   [CDC (Coarse+Fine)] is the start value.

 

If you have any other questions, please kindly ask here and we will try to resolve it.


Best regards,

 

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

Thank you for the explaination!  I appreciate the help from this forum over the years!

 

Best,

Ron

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors