CODEWITHSUNDEEP
powerbidax
variable
variable

Reputation: 9714

How to resolve `single value for column cannot be determined` error?

DimUser and DimCustomer filter the FactSales table.

I have created a RLS role with the following DAX on the DimCustomer table:

[DW_CustomerID] IN
SELECTCOLUMNS(FILTER(Dimuser,DimUser[User_Email]=USERPRINCIPALNAME())
, "DW_CustomerID", FactSales[DW_CustomerID])

My intention is to filter the DimUser based on the current user's email, then retrieve the filtered Customer ID's from the FactSales table. Effectively the logged in user can only those customer for the user has made sales.

The DAX is giving following error:

A single value for column DW_CustomerID in table FactSales cannot be determined.

How to resolve this error?

Upvotes: 0

Views: 5788

Answers (1)

Antyan
Antyan

Reputation: 43

You are looking for a column that does not exist in the table you are looking for it in. The first parameter of the SELECTCOLUMNS() function is a table, in your case you have provided a derived table built by the FILTER() function being used on the DimUser table. Therefore, your derived table is one row with all the columns from the DimUser table. The FactSales[DW_CustomerID] column is not in this table.

I would try rewriting it to be something closer to the following:

[DW_CustomerID] IN 
  CALCULATETABLE(
    VALUES(FactSales[DW_CustomerID]), 
    DimUser[User_Email] = USERPRINCIPALNAME()
  )

Without seeing your model it is tough to know for sure though.

Upvotes: 0

Related Questions