How to filter values in column and map the filtered column to slicer

[Maggi029]

I have a column that looks like this; I need to filter the column only with the main version and the most recent development version and map it to slicer.

Current Column:

Version

"4.3.2"

"5.3.2"

"6.3.2"

"development_7.10"

"development_7.11"

Expected Filtered Column or Measure:

Version

"4.3.2"

"5.3.2"

"6.3.2"

"development_7.11"

@MFelix  could you please answer this

[MFelix · Accepted solution]

Hi @Maggi029 .

For this I believe you need an additional column to make the Development higlight.

On the power query create a new column with the following code:

if Text.AfterDelimiter ([Version] , "_") <> "" then 

Text.PadStart( Text.AfterDelimiter ([Version] , "_"), 6, "0") else ""

I'm assuming that the developement versions will only have 2 number after the dot this will return the following code:

Now you can add a flag on DAX:

Slicer Flag = if('Table (2)'[Development_Code] = BLANK() || 'Table (2)'[Development_Code] = MAX('Table (2)'[Development_Code]), 1)

If you want to do it only on the Power Query then you can do the following steps:

• Add a custom column with the following code:

if Text.AfterDelimiter ([Version] , "_") <> "" then 

"Development" else [Version]

• Do a group by on this column wiht the selection of the maximum of the version number:

• Now do a merge of the table with itself:

• Change the step to get the step before the new column (in my case is the source):

• Expand the columns making a count:

You can replace the 0 by null or just filter out on the slicer.

[Maggi029]

Hi @MFelix 

There is one more requirement in this case, I need to last item(eg: Developement_7.11) is always preselected in slicer.how to achieve that

In other words

I've assigned the above version column to a slicer and want the last item (Development_7.11) to be selected in the dropdown slicer whenever I load the power bi report.

How to achieve the above scenario?

[MFelix]

Hi @Maggi029 ,

There is no direct way of doing this, do you need that the number of the Development is kept on the slicer or would it be enough just to have development on the slicer and then show that number somewhere else?

[Maggi029]

Hi @MFelix 

Please ignore above query.

Actually I have another requirement related to main question, can you help me to do this.

Requirement:

Is there any way to set the default value based on the condition?

Example : I have a number column and a flag. Here I need to have a slicer with number columns and the default selection would be based on the flag where the flag value=Y then that related number value has to be selected by default.

[MFelix]

Hi @Maggi029 , 

Has refered there is no direct option to  select a value on a slicer you can do certain workaorounds.

Can you please tell me how you have the data?

[Maggi029]

Hi @MFelix 

You mean the data format.

As provided in the description , I have a data table has Version column and flag column(created as per your suggestion), datatable will be updated regularly at certain interval.

I have mapped the version column to slicer and i want to be set slicer with default selection, as provided here

Please let me know, how you are expecting to describe my data?

[MFelix]

Hi @Maggi029 ,

The Slicer you present is fine in terms of what I need. In this case I would purpose to you to pick the column with the flag, filter out the blank values and select the one from developem to be your default, that way when you have the refresh of the data you would get always the latest one:

Has you can see my current one has a generic name if you don't make any changes to that genereic name the filter will always be picked up. You then can add a card or other type of visualization in order to identify the code.

[Stemar_Aubert]

I did something simlar a while ago to have a slicer always show the "actual" month.

Create another column in Powerquery and use conditional statements to fill "last version" and "last dev version" (or any other text suitable for you) next to 5.3.2 and Development_6.11 :

Version	SlicerText
"4.3.2"	"4.3.2"
"5.3.2"	Last version
"development_6.11"	Last development version

You can then map this column to the slicer and click on what you want to display as default. Updating the table through powerquery, you can have the users always land on "Last version", regardless of what you have above.

To automate the process, depending on how you add versions to your table (I imagine manually?) you could create an index in PowerQuery and evaluate the last two rows based on the index. There are others ways, that one is just the first that comes to mind. 

[Maggi029]

Hi @Stemar_Aubert 

I need to automate the process so that  

"The slicer should only contain version numbers (e.g., "4.3.2") and the most recent development (e.g., "development 7.11") values; I need to remove the in-between version ("development 7.10") so that the slicer only contains the values listed below."

Can you please share any power query formula to do this filtering?

[amitchandak]

@Maggi029 ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[Maggi029]

Hi @amitchandak 

I need to map the column values listed below to a slicer while excluding some of the values in the column.

 Column:

Version

"4.3.2"

"5.3.2"

"6.3.2"

"development_7.10"

"development_7.11"

Conditions:

1.The slicer should only contain version numbers (e.g., "4.3.2") and the most recent development (e.g., "development 7.11") values; I need to remove the in-between version ("development 7.10") so that the slicer only contains the values listed below.

Slicer values:

Version

"4.3.2"

"5.3.2"

"6.3.2"

"development_7.11"