Cannot Identify the table that contain column

[Sri_phani]

Hi team, 

Kindly help me with this. 

I executed this table measure in the DAX query view. The problem with this is, that I am unable to use this in Measure and show value in the card. the objective is to identify the rank of employees between selected dates and show it in the card. So I tried this approach of creating the virtual table and adding the columns. This works fine in the Query view, but when I try to use calculate to convert it into measure. it's saying the rank column cannot be found. 

Here is the screenshot of the DAX Query view (it's working fine) : 

I did some search and found the data linage can effect this kind of approach. But couldn't figure out how to fix the below problem. 

I even tried removing resolve numerator and denominator and replace it with directly "Resolve" measure I created but no change. 

Test file - Google Sheets ( This data is exact replica of my dataset, except removed the confidentia...

VAR Tabled =
    SUMMARIZE(
                FILTER(
                    'Metric Table',
                    'Metric Table'[Date] <= MAX('Metric Table'[Date]) &&
                    'Metric Table'[Date] >= MIN('Metric Table'[Date]) &&
                    'Metric Table'[Site + LOB] = "Hyderabad - Rider"
                ),
                'Metric Table'[Employee Name],
                'Metric Table'[Site + LOB]
            ,
            "Resolve Numerator", SUM('Metric Table'[Resolve Numerator]),
            "Resolve Denominator", SUM('Metric Table'[Resolve Denominator]))

VAR newtable = SELECTCOLUMNS(Tabled,
			'Metric Table'[Employee Name],
			 "Resolve1", [Resolve Numerator]/[Resolve Denominator]
				)
VAR test1 = SELECTCOLUMNS(newtable,
			'Metric Table'[Employee Name], 
			"attain", [Resolve1]/0.60
				)
VAR test_filter = FILTER(test1, 
			[attain]<>BLANK()
			)
VAR Ranking = SELECTCOLUMNS(test_filter,
				'Metric Table'[Employee Name],"Rank1", 
				RANKX(test_filter, [attain],, DESC,Dense)
			)
VAR test2 =  FILTER(Ranking, 
			'Metric Table'[Employee Name] = "Santosh Kumari"
		)
VAR test4 = SELECTCOLUMNS(test2, "Only Rank",[Rank1])

RETURN
test4

[Dangar332 · Accepted solution]

Hi, @Sri_phani 

As i understood your problem you have Three slicer in page
1.  Employee Name  

2. site+LOB

3. Dates 

Try below measure 

create Measure for attain_measure

attain_measure=
var Numerator = SUM('Metric Table'[Resolve Numerator])
var Denominator = SUM('Metric Table'[Resolve Denominator]))
return
(divide(Numerator,Denominator))/0.60

Create RankX_measure for your Card visuals 
use below measure for your Card visuals

RankX_measure  =
var Table_ = 
addcolumn(
filter(
all('Metric Table'[Employee Name], 'Metric Table'[Site + LOB] ),
 'Metric Table'[Site + LOB] = "Hyderabad - Rider"),
"attain",[attain_measure])
return
rankx(Table_,[attain_measure],,desc,dense)

Best Regards,
Dangar

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sri_phani]

Hi @Dangar332 . 

Thank you for responding! 

I only have employee name and Date as slicers. But the objective is to identify the rank amount particular LOB ( team). Example : total 200 employees are there. I don't need rank among 200 employees. I need rank among team of 50 employees. For that I tried above approach. Does your measure, help me with my problem?

[Dangar332]

Hi, @Sri_phani 

Please try my updated Rankx measure; it might fix your issue.

Best Regards,
Dangar

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sri_phani]

Hi @Dangar332  This worked like magic. You don't know how much relieved I am. I worked like crazy on this logic since last 2 days. Thank you very much. Have a great day!

[Dangar332]

Hi, @Sri_phani 

Thank you

Have a good day.