topic Cannot Identify the table that contain column in DAX Commands and Tips

Cannot Identify the table that contain column

Sri_phani — Sun, 04 Aug 2024 17:24:07 GMT

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 confidential data).

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

Re: Cannot Identify the table that contain column

Dangar332 — Sun, 04 Aug 2024 19:00:01 GMT

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.

Re: Cannot Identify the table that contain column

Sri_phani — Sun, 04 Aug 2024 18:34:59 GMT

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?

Re: Cannot Identify the table that contain column

Dangar332 — Sun, 04 Aug 2024 19:02:07 GMT

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.

Re: Cannot Identify the table that contain column

Sri_phani — Sun, 04 Aug 2024 19:40:15 GMT

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!

Re: Cannot Identify the table that contain column

Dangar332 — Sun, 04 Aug 2024 19:44:01 GMT

Hi, Sri_phani

Thank you

Have a good day.