Direct Query - the column was specified more than once in the groupcrossapplytable function

Power BI
1

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

ClientName Country Size Code
JohnSmith US Large 500
HomerSimpson UK Large 550
PeterGriffin US Small 300
LukeSkywalker US Large 900
ObiWan DE Small 300

Sharepointlist_Code

DIM1 DIM2 Code
Macy Sweets 500
Walmart Sweets 300
Walmart Tools 900

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

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?