cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver I
Resolver I

A table of multiple values was supplied where a single value was expected.

CRM Log has Many-to-one relatioship with other tables.

Capture11.PNG

1 ACCEPTED SOLUTION

@ziyabikram96  - Right, you could replace what you have with something like this:

 

CC StartDate =
  SWITCH(TRUE(),
    MAXX(RELATEDTABLE('CRM Log'),[CS Referred To]) = "Supply Chain",LOOKUPVALUE(....),
    MAXX(RELATEDTABLE('CRM Log'),[OPS Referred To]) = "Supply Chain",LOOKUPVALUE(....),
    MAXX(RELATEDTABLE('CRM Log'),[SC Referred To]) = "Supply Chain",LOOKUPVALUE(....),
    BLANK()
  )

 

The other thing that is causing your issues are your DISTINCT statements. That returns a table of values and then you are trying to use it as a scalar. That won't work and is actually probably the source of the error you are getting.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Super User IV
Super User IV

@ziyabikram96 - Pretty sure you can't nest SWITCH statements like that unless you are using SWITCH(TRUE()...) I would "switch" that DAX to use a single SWITCH(TRUE()...) statement, what you have there is kind of an abomination. No offense.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Super User IV
Super User IV

@ziyabikram96 , difficult to tell. check each lookup separately. it should return only one value



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

I am trying to calculate a calculated column through this expression. The current table has one-to-many relationship with CRM Log.

ziyabikram96_0-1596124822629.png

 

@ziyabikram96  - Right, you could replace what you have with something like this:

 

CC StartDate =
  SWITCH(TRUE(),
    MAXX(RELATEDTABLE('CRM Log'),[CS Referred To]) = "Supply Chain",LOOKUPVALUE(....),
    MAXX(RELATEDTABLE('CRM Log'),[OPS Referred To]) = "Supply Chain",LOOKUPVALUE(....),
    MAXX(RELATEDTABLE('CRM Log'),[SC Referred To]) = "Supply Chain",LOOKUPVALUE(....),
    BLANK()
  )

 

The other thing that is causing your issues are your DISTINCT statements. That returns a table of values and then you are trying to use it as a scalar. That won't work and is actually probably the source of the error you are getting.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Terrific!! Thanks alott!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors