Forum Discussion

JacobMich's avatar
JacobMich
Regular Visitor
1 year ago
Solved

How do I filter POWERBI data in my visual workspace based on values in two columns?

I am working in PowerBI.  I am looking for a specific filtering and counting of information from an already transformed query.  The raw data of what I am asking for can be seen in the Table Below.  I want to only count and show all "US" AND "5", as well as count and show all MX "4".  I want to leave out US "4" and leave out MX "5", in the count scenario.

CountryLevel

US

4
US5
US4
MX5
MX4
US5
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Filtered Count =
    CALCULATE(
    COUNTROWS('YourTableName'),
    FILTER(
    'YourTableName',
    ('YourTableName'[Country] = "US" && 'YourTableName'[Level] = 5)
    || ('YourTableName'[Country] = "MX" && 'YourTableName'[Level] = 4)
    )
    )

    Replace with Table Name as per yours

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Filtered Count =
    CALCULATE(
    COUNTROWS('YourTableName'),
    FILTER(
    'YourTableName',
    ('YourTableName'[Country] = "US" && 'YourTableName'[Level] = 5)
    || ('YourTableName'[Country] = "MX" && 'YourTableName'[Level] = 4)
    )
    )

    Replace with Table Name as per yours

    • JacobMich's avatar
      JacobMich
      Regular Visitor
      1 year ago

      Where do I place the above code and syntax?

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Create a Dax - New measure

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    1 year ago

    JacobMich 

    how do you want to display the output? in the card or in a table?

    You can try Anonymous 's solution. That will work

     

    if you want to display in a card, add the meausre to the card

     

    if you want to display in a tabel, try to update the measure

    measure 2 = if(max('Table'[Country])="US" &&max( 'Table'[Level])=5 || max('Table'[Country])="MX"&& max('Table'[Level])=4,1)
     
    and add the measure to visual filter and set to 1
     
    pls see the attachment below
  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    Hi JacobMich 

    I'm not sure what you expected results are but try these measures:

    US and 5 = 
COUNTROWS ( FILTER ( 'Table', 'Table'[Country] = "US" && 'Table'[Level] = 5 ) )

    MX and 4 = 
COUNTROWS ( FILTER ( 'Table', 'Table'[Country] = "MX" && 'Table'[Level] = 4 ) )

    Both = 
COUNTROWS (
    FILTER (
        'Table',
        ( 'Table'[Country] = "US"
            && 'Table'[Level] = 5 )
            || ( 'Table'[Country] = "MX"
            && 'Table'[Level] = 4 )
    )
)

     

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi JacobMich,

    I hope you had a chance to review the solution shared by danextian ryan_mayu Anonymous . If it addressed your question, please consider accepting it as the solution — it helps others find answers more quickly.
    If you're still facing the issue, feel free to reply, and we’ll be happy to assist further.

     

    Thank you.