Alphabetical 'between' argument?

hI GlassShark1 

Download this sample PBIX file

You can create a column using this DAX

SubCat = SWITCH(

    TRUE(),

    MID([Category], 1,1) IN {"A", "B", "C"}, "Dairy",

    MID([Category], 1,1) IN {"D", "E", "F"}, "Meat",

    "Fruit"
)

Regards

Phil

Hi GlassShark1 

Please @ mention me in replies or I won't be notified of your reply.  Type @ then select my name.

Download Power Query solution

MID is a bit of a misleading in this case as it's returning the first character from the string, so just A, B etc.  This is required to check against the list of characters with the IN operator.

SWITCH acts like a multi-nested IF.  Using TRUE as the 1st expression allows you to then match any subsequent expressions, and if they evaluate to TRUE then that result is chosen.  In this case I'm checking the first letter from each string and when a match is found, the appropriate sub-cat string is returned.

Please see fiel linked to above for a Power Query solution.

The code for this is in the Table2 query.  It works in a similar way to the DAX solution, getting the first character from the Category and checking that against a string e.g."ABC" then returning the appropriate Subcat.

regards

Phil

Eventually figured it out in Power Query - something like the below works with lists:

Dairy_List = {"A".."C"},
Meat_List = {"D".."E"},

<rest of the code>

 #"Stepforthis" = Table.AddColumn(#"LastStep", "Main Category", each
if List.Contains(Dairy_List,[Letter]) then "Dairy" else
if List.Contains(Meat_List,[Letter]) then "Meat" else
"Whatever"
)

Thanks 🙂