cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GlassShark1
Helper III
Helper III

Alphabetical 'between' argument?

I've a data set where categories in a column have letter prefixes (e.g. A. Butter, B. Milk, C. Yogurt), and then are summarised by a total for those categories in another row (e.g. 'Dairy'). What i want to do (either through Power Query or DAX) is to construct an argument and create a column like below, to show which main category each subcategory actually belongs to (pseudo-code):

If [Product letter] >= "A" and [Product letter] <= "C" then "Dairy" else
If [Product letter] >= "D" and [Product letter] <= "F" then "Meat" etc etc

I've seperated out the letters, but does PBI have this kind of text operator - to recognise that 'C' comes after 'B' for example?

Thanks

3 ACCEPTED SOLUTIONS
PhilipTreacy
Super User
Super User

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

 

 

cats.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

PhilipTreacy
Super User
Super User

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.

pqcats.png

 

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

GlassShark1
Helper III
Helper III

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 🙂

View solution in original post

6 REPLIES 6
GlassShark1
Helper III
Helper III

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 🙂

PhilipTreacy
Super User
Super User

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.

pqcats.png

 

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Thank you @PhilipTreacy - learned alot from you this morning! Wouldn't all SWITCH formulas start with TRUE() then? Still wrestling with this bit. Maybe i'm struggling because it's a boolean? I'll keep trying to figure it out anyway - thanks again 🙂

Hi @GlassShark1 

Not all SWITCH use TRUE() but you see it a lot.

You could use SWITCH to test a value e.g.

SWITCH(

   MyVal,

   1, "Alpha",

   2, "Beta",

   3, "Gamma",

   0
)

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


PhilipTreacy
Super User
Super User

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

 

 

cats.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Brilliant - i did think about lists but really not sure about them in PBI yet. Thanks 🙂

I'm just trying to break this down so i understand it. Does the MID argument add anything? Seems fine without it? 

I've never used SWITCH before - the docs seem to say it basically uses comma seperated expressions then the outomes if those expressions are true which makes sense. What is the TRUE() element doing at the begining though? I would read this as 'expression1', so i'm not sure how it works...

Annoyingly i've now twigged i probably need a solution in Power Query anyway, as i need to use the outcomes of this to make a DIM table...this will doubtless be useful though - thank you!

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors