Forum Discussion
Need Help - Extracting Values from a Column then Filter on those Values
To Support Community:
I have a column of data in a table that looks like this:
| 1 |
| 1.1 |
| 1.1.1 |
| 1.1.1.1 |
| 1.1.2 |
| 1.1.2.1 |
| 1.2 |
| 1.2.1 |
| 1.2.1.1 |
| 1.2.2 |
| 1.2.2.1 |
| 1.3 |
| 1.3.1 |
| 1.3.1.1 |
| 1.3.2 |
| 1.3.2.1 |
| 1.4 |
| 1.4.1 |
| 1.4.1.1 |
| 1.4.2 |
| 1.4.2.1 |
The column is Text.
- Requirement #1: I need to extract all from the values from the column that are in this format: #.#. Using the above, the extract should pull: 1.1, 1.2, 1.3, and 1.4. I will not know how many exist in the column.
- Requirement #2: I will then need to create a filter based on what was extracted.
I have looked into all of the DAX Text functions and, I know if the values was "Fixed", I could use some combo of SEARCH/FIND/LEN, but when I only know the format, I am having difficulty. Additionally, the filter is giving me a little trouble.
Any help would be appreciated,
Kevin
Here is one way. This will make a list of the values meeting your criteria which you can then convert to a table to be used in a slicer. You could also use the same logic of List.Count(Text.Split([TextColumn, "."))=2 in a custom column with if..then.else to create a column to filter by in the next step. For this one, just right click last step and "Insert Step After" and use this M code, referencing your text column name.
= List.Select(#"Changed Type"[TextColumn], each List.Count(Text.Split(_, "."))=2)
Regards,
Pat
3 Replies
- Nathaniel_C
Community Champion
Hi Anonymous
If I understood you,
Your table:The measure:
Extraction = (IF(Len(MAX('Extract'[Values])) = 3, Max('Extract'[Values])))The result:
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel - mahoneypat
Microsoft Employee
Here is one way. This will make a list of the values meeting your criteria which you can then convert to a table to be used in a slicer. You could also use the same logic of List.Count(Text.Split([TextColumn, "."))=2 in a custom column with if..then.else to create a column to filter by in the next step. For this one, just right click last step and "Insert Step After" and use this M code, referencing your text column name.
= List.Select(#"Changed Type"[TextColumn], each List.Count(Text.Split(_, "."))=2)
Regards,
Pat
- HotChilli
Can't you test the length of the string to see if it equals 3?
