Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MRensenSDR
Regular Visitor

Filter value from column with multiple values

Hi there!

 

I'm having trouble figuring out how to solve the following problem. I have a table in which I want to filter on a column. The table contain the following:

Example:

GroupcodeValue
BK; WKA; 4; VGM; OVx
BK; WKA; OVx
OVx
BK; WKA; 4; VGMx
BK; WKA; VGM; 6x
OV; 6x

 

In this case only the 'OV' rows are relevant for the enduser. But if the enduser only wants to filter this he/she has to check all the different versions with 'OV' in them to have a correct filter. One option is to replace the values with 'OV' in them to OV so the filter only has the 'OV' option. The problem here is that I don't know all the different combinations beforehand, so this could result in breaking the dashboard or missing data. Is possibly some sort of formule in which I can user a sort of "lookup" function to combat this problem? Thanks!

1 ACCEPTED SOLUTION

If you know in advance about all such cases, you could handle it as follows...

 

Flavour = 
VAR CheckOV = SEARCH("OV",FlavourData[Groupcode],1,BLANK())
VAR Check2 = SEARCH("2",FlavourData[Groupcode],1,BLANK())
VAR CheckX = SEARCH("X",FlavourData[Groupcode],1,BLANK())
VAR CheckOH = SEARCH("OH",FlavourData[Groupcode],1,BLANK())
VAR CheckH = SEARCH("H",FlavourData[Groupcode],1,BLANK())
VAR Result =
    SWITCH(
        TRUE(),
        NOT(ISBLANK(CheckOV)),"Contains OV",
        NOT(ISBLANK(Check2)),"Contains 2",
        NOT(ISBLANK(CheckX)),"Contains X",
        NOT(ISBLANK(CheckH)) && ISBLANK(CheckOH),"Contains H",
        NOT(ISBLANK(CheckOH)),"Contains OH",
        "Wrong Value"
    )
RETURN Result

If you don't know about all such cases, then you have to figure out some other way.

View solution in original post

16 REPLIES 16
sreenathv
Solution Sage
Solution Sage

sreenathv_0-1617953834602.png

You could add another calculated column. Refer to the image above. Then use the new calculated column in filters/slicers.

Change the IF condition as per your need.

 

Thanks for the reply, this would seem to do the trick! Is it also possible to extend the search parameter? In this example I only used "OV", but in reality I have a few more "flavours" that have the same problem. Is it possible to do these results in 1 column? Thanks!

sreenathv_0-1617959516373.png

 

You can add more OR conditions using || to the IF condition

 

If you don't want to add so much DAX code, then alternatively you can tell the users to use the advanced filter option of the visuals.

 

sreenathv_1-1617959803956.png

But the options will be limited to 1 or 2 values at a time.

 

The best option is to automatically generate a table for filtering in PowerQuery, create a relationship and use that PowerQuery generated table for filtering.... Refer to the image below...

 

sreenathv_0-1617962086735.png

 

 

Thank you so much for the detailed answer! We are getting close, the DAX formula seems to be the solution but is it correct that it can only contain 2 different outputs? It's always does contain or not contain. The results I need are the differrent "flavours" I know beforehand, so it has to be more than 2 of them.

The automatically generated table option seems to not have this problem. Could you please explain me how to create this filtered table? You've helped me so much already, thanks!

 It's always does contain or not contain. The results I need are the differrent "flavours" I know beforehand, so it has to be more than 2 of them.

 

Using nested if condition or SWITCH function, we can populate as many values as we want in a field. But in your cases, the GroupCode has more than one flavour in it. Therefore, you are forced to give only two outputs.

 

The automatically generated table option seems to not have this problem. Could you please explain me how to create this filtered table? You've helped me so much already, thanks!

 

I can help you with the power query. For that, please post the autogenerated powerquery of your existing table. In case you don't know, it is available at Transform->Advanced Editor.

 

 

A SWITCH function could possibly work I guess. My "flavours" will always be a single value. It cannot happen that there will be more then 1 "flavour" in the groupcode. 

That said, the power query of my existing table with the groupcodes in them is:


 

let
    Bron = Excel.Workbook(File.Contents("C:\Users\michael.rensen\OneDrive - SDR\Bureaublad\PowerBi Hans\SegmentenOpdrachtgever rapport Hans.xlsx"), null, true),
    Atrium_Sheet = Bron{[Item="Atrium",Kind="Sheet"]}[Data],
    #"Headers met verhoogd niveau" = Table.PromoteHeaders(Atrium_Sheet, [PromoteAllScalars=true]),
    #"Type gewijzigd" = Table.TransformColumnTypes(#"Headers met verhoogd niveau",{{"Relatiegroep", type text}, {"Aanmaakdatum", type datetime}, {"Opbrengst", type number}, {"Kostenbedrag", type number}, {"Winst/verlies % (F)", type number}, {"Hoofdproject code", type text}, {"Aantal uur gecalculeerd", type number}, {"Winstbedrag (totaal)", type number}, {"Geboekte uren", type number}, {"Opdrachtgever korte naam", type text}, {"Kosten gecalculeerde uren", type number}, {"Kosten uren werkelijk", type number}, {"Opdrachtgever code", Int64.Type}, {"Hoofdproject titel", type text}}),
    #"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Type gewijzigd", "Segment", each null),
    #"Namen van kolommen gewijzigd" = Table.RenameColumns(#"Aangepaste kolom toegevoegd",{{"Hoofdproject code", "Project Nr."}})
in
    #"Namen van kolommen gewijzigd"

 

 The column 'Relatiegroep' is the one with all the different flavours with the "noise" in them that needs to be filtered. I have another table with all the "flavours" listed.

Basically I would need something like this, but the following code is obviously not working as intended. Is there a way to rewrite this to make it work?
PBI.PNG

 

From "Get Data" choose the new "Blank Query" and paste the following code. Do not modify your existing table's powerquery.

let
    Bron = Excel.Workbook(File.Contents("C:\Users\michael.rensen\OneDrive - SDR\Bureaublad\PowerBi Hans\SegmentenOpdrachtgever rapport Hans.xlsx"), null, true),
    Atrium_Sheet = Bron{[Item="Atrium",Kind="Sheet"]}[Data],
    #"Headers met verhoogd niveau" = Table.PromoteHeaders(Atrium_Sheet, [PromoteAllScalars=true]),
    #"Type gewijzigd" = Table.TransformColumnTypes(#"Headers met verhoogd niveau",{{"Relatiegroep", type text}, {"Aanmaakdatum", type datetime}, {"Opbrengst", type number}, {"Kostenbedrag", type number}, {"Winst/verlies % (F)", type number}, {"Hoofdproject code", type text}, {"Aantal uur gecalculeerd", type number}, {"Winstbedrag (totaal)", type number}, {"Geboekte uren", type number}, {"Opdrachtgever korte naam", type text}, {"Kosten gecalculeerde uren", type number}, {"Kosten uren werkelijk", type number}, {"Opdrachtgever code", Int64.Type}, {"Hoofdproject titel", type text}}),
    #"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Type gewijzigd", "Segment", each null),
    #"Namen van kolommen gewijzigd" = Table.RenameColumns(#"Aangepaste kolom toegevoegd",{{"Hoofdproject code", "Project Nr."}}),
    
    ListOfGroupCodesList = List.Distinct(Table.Column(#"Namen van kolommen gewijzigd","Relatiegroep")),
    ListOfGroupCodesTable = Table.RenameColumns(Table.FromList(ListOfGroupCodesList),{{"Column1","Relatiegroep"}}),
    AddCodes = 
        Table.AddColumn(
            ListOfGroupCodesTable,
            "Flavour",
            each Text.Split(Record.Field(_,"Relatiegroep"),";")
            ),
    ExpandFlavour = 
        Table.RenameColumns(
            Table.RemoveColumns(
                Table.AddColumn(
                        Table.ExpandListColumn(AddCodes, "Flavour"),"Flavour2",each Text.Trim(Record.Field(_,"Flavour"))
                ),
                {"Flavour"}
            )
            ,{{"Flavour2","Flavour"}}
        )
in
    ExpandFlavour

 

Hopefully, it will give you a new table with all the individual values. You can then create a relationship between your flavours to this table and another relationship from this table to the data table. Not sure, but based on your data model you decide.

Thanks for the reply! I used the power query to add the new table. And indeed, it has al the differt combinations in the rows. But when I make the relation between the tables and want to filter the results i
-Get the good rows, but also some other non relevant rows

-Rows that are duplicated in the view (x amount times that the group code has different values).

Is this an easy fix? Or am I doing something completely wrong?


Is it perhaps a better solution to rewrite this into something that works with a SWITCH statement? Thanks!

PBI.PNG

Flavour = 
VAR CheckOV = SEARCH("OV",FlavourData[Groupcode],1,BLANK())
VAR Check2 = SEARCH("2",FlavourData[Groupcode],1,BLANK())
VAR CheckX = SEARCH("X",FlavourData[Groupcode],1,BLANK())
VAR Result =
    SWITCH(
        TRUE(),
        NOT(ISBLANK(CheckOV)),"Contains OV",
        NOT(ISBLANK(Check2)),"Contains 2",
        NOT(ISBLANK(CheckX)),"Contains X",
        "Wrong Value"
    )
RETURN Result

This is awesome! It seems to be working, although with one caveat. When I have 2 values; example: OH and H. With the H selection also OH will show up because it has the letter H in it. Is there somehow any way to combat this to? If this isn't possible I'll find another way around it. But if this small issue could also be resolved this would work magically for me!

If you know in advance about all such cases, you could handle it as follows...

 

Flavour = 
VAR CheckOV = SEARCH("OV",FlavourData[Groupcode],1,BLANK())
VAR Check2 = SEARCH("2",FlavourData[Groupcode],1,BLANK())
VAR CheckX = SEARCH("X",FlavourData[Groupcode],1,BLANK())
VAR CheckOH = SEARCH("OH",FlavourData[Groupcode],1,BLANK())
VAR CheckH = SEARCH("H",FlavourData[Groupcode],1,BLANK())
VAR Result =
    SWITCH(
        TRUE(),
        NOT(ISBLANK(CheckOV)),"Contains OV",
        NOT(ISBLANK(Check2)),"Contains 2",
        NOT(ISBLANK(CheckX)),"Contains X",
        NOT(ISBLANK(CheckH)) && ISBLANK(CheckOH),"Contains H",
        NOT(ISBLANK(CheckOH)),"Contains OH",
        "Wrong Value"
    )
RETURN Result

If you don't know about all such cases, then you have to figure out some other way.

Thank you so much man! These cases I should know beforehand. I can make it work this way. Thank you so much for your time and this solution. I also learned a lot from this!

 

I also learned a lot from this!

Talk about learning, here is one more thing to learn about the SWITCH() function. This function terminates when the condition returns TRUE for the 1st time and it won't execute any further. You can actually leverage this "knowledge" to handle the situation just by adjusting the order of the conditions in SWITCH(). See the results below...

sreenathv_0-1618213431519.png

Apparently, both are the same formula, but different results. Usually, I don't recommend it unless the user completely understands how the function works rather than just knowing the syntax of a function.

 

That is awesome! And could possible indeed also work for my reasoning. The SWITCH function will execute to a true result (or eventually false) each unique row right? If so this will work for my purpose!

Yes. You can use it in your scenario. But if you are not careful in your ordering of conditions, you might get the wrong result and the end-user won't even realize it. That is why I suggested the explicit multi-condition using && operator. If you explicitly say that I want "H" but don't want "OH" in the condition, it will always work irrespective of the ordering. Be careful.

Thanks for the explaination and all your help!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.