Forum Discussion

Luke-Howells's avatar
Luke-Howells
Frequent Visitor
4 years ago

Calculated Column HELP! Variable from Slicer

Hi Experts,

 

So. . . 

 

I have a Driver table which contains a quote ID and a driving licence type. Some quote numbers have more than 1 driving licence type and some only have one. I have another table called Licence Type which contains a unique list of all available licence types (I plan on using this as the slicer). 

 

Ideally what I want to do is use the slicer to select licence types that would be accepted and what ever isn't selected would be not accepted. I then want a column in the driver table to show for each record if it would be accepted or not. Now I kinda have this working with the below, HARD CODED would be the column which show acceptabillity, my only problem is the licence types that are accepted are currently written into the formula (hence the name HARD CODED). HARD CODED SWITCH just takes the result and displays an accept or decline slicer. 

 

The end result would show a unqiue list of quote ID's and wether they are accepted or declined, not to forget that some quotes have more than 1 licence type so if one of those licence types was a non accepted licence type then the whole quote would be a decline. 

 

My problem: So I have the below which works apart from the accepted licence types are hard coded (as previosuly mentioned). How can I have that the accepted licence types for the calculated column come from the results of a slicer? or something similar?

 

HARD CODED = IF('01. Data [Driver]'[LicenceType] IN { "F", "P" } , 1 , 999 )

 

HARD CODED SWITCH = IF('01. Data [Driver]'[HARD CODED] >= 999,"Decline","Accept")

 

2 Replies