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

[roncruiser]

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

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.

[v-lid-msft]

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,

[roncruiser]

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!

[roncruiser]

Updated the first post with further clarification.

Thanks!

[v-lid-msft]

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]

Best regards,

[roncruiser]

@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!
    )

[v-lid-msft]

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,

[roncruiser]

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

Best,

Ron