Forum Discussion

DataGuest's avatar
DataGuest
Frequent Visitor
2 years ago

Direct Query - the column was specified more than once

Hello

 

I have below tables in my data model.

1) SharePointlist_Account (Direct Query)

2) SharePointlist_Account_Dis (Direct Query)

3) Sharepointlist_Code (Import)

4) Sharepointlist_Code_Dis (Import)

5) LossTable

6) ViewPreferenceTable

 

1 & 2 is the same data source, 3 & 4 as well.

 

Relationships:

- LossTable ManyToOne SharePointlist_Account (bidirectional filter)

- SharePointlist_Account ManyToOne Sharepointlist_Code (bidirectional filter)

- Sharepointlist_Code_Dis OneToMany SharePointlist_Account_Dis 

 

SharePointlist_Account

ClientNameCountrySizeCode
JohnSmithUSLarge500
HomerSimpsonUKLarge550
PeterGriffinUSSmall300
ObiWanDESmall300

 

Sharepointlist_Code  

DIM1DIM2Code
MacySweets500
WalmartSweets300
WalmartTools900

 

Need:

DISPLAY

There is a slicer based in "ViewPreferenceTable" with two options ("By Industry" and "By Country")

When user selects JohnSmith in the slicer:

* it should keep in the table only records where Size = Large (the same as JohnSmith/user selection)

OR

* it should keep in the table only records where Country = US (the same as JohnSmith/user selection)

This is achieved by disconnected table. SharePointlist_Account is used as a slicer and SharePointlist_Account_Dis is used to populate table.

 

Measure looks like this: 

VAR _Selected_Industry_Disconnected = SELECTEDVALUE (Sharepointlist_Code_Dis [DIM2])
VAR _Selected_Industry_Standard = SELECTEDVALUE (Sharepointlist_Code[DIM2])
VAR _Selected_Country_Disconnected = SELECTEDVALUE (SharePointlist_Account_Disc[Country])
VAR _Selected_Country_Standard = SELECTEDVALUE (SharePointlist_Account[Country])
VAR _preference = SELECTEDVALUE ( ViewPreferenceTable[Preference] )
RETURN
SWITCH ( _preference,
"By Industry",
IF(
_Selected_Industry_Disconnected = _Selected_Industry_Standard,
1),
"By Country",
IF(
_Selected_Country_Disconnected = _Selected_Country_Standard,
1)
)

 

COLOR

Now I want to create a measure that will put "1" for this client that is selected in the slicer above, but it doesn't work. So if I select "JohnSmith" from the slicer (based on SharePointlist_Account) , it will keep all the records in the table (sourced from SharePointlist_Account_Disc) but will put "1" for the row with JohnSmith.

 

It gives me an error that the column was specified more than once in the groupcrossapplytable function.

 

Does anyone have an idea on how it can be solved?

1 Reply

  • SharePoint lists in Direct Query Mode?  I don't think that is possible.

     

    Please provide sample data that fully covers your issue.
    Please show the expected outcome based on the sample data you provided.