Forum Discussion

sebouier's avatar
sebouier
Frequent Visitor
2 years ago
Solved

Conditional value in Filtering context

Hi,

 

Here's my table :

IdNameCat
1Boba
2Allanb
3Allanc
4Jasone
5Jasonf

 

User is selecting cat b and f so it shows row number 2 and 5.

 

How to show a table with rows number 2, 3, 4, 5 (because of the identical names) and also have a flag on rows 2 and 5?

Many thanks for your help.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi sebouier ,

    AnkitaaMishra Thanks for your reply!

    And sebouier , please try this way:
    First use this DAX to create a new table for slicer:

    Slicer = VALUES('Table'[Cat])

    Note: without any relationship!

    Then use this DAX to create a measure:

    Measure = 
VAR _Cat = VALUES(Slicer[Cat])
VAR _Name = MAX('Table'[Name])
RETURN
IF(
    CALCULATE(
        COUNTROWS(
            FILTER(
                'Table',
                'Table'[Name] = _Name && 'Table'[Cat] IN _Cat
            )
        ),
        ALLSELECTED('Table')
    ) > 0,
    1,
    0
)

    Set it up as shown in the following figure:

    Then use this DAX to create another measure:

    Flag = 
IF(
    MAX('Table'[Cat]) IN VALUES(Slicer[Cat]),
    1,
    0
)

    And put this measure into the table visual:


    The final output is as below:


    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • AnkitaaMishra's avatar
    AnkitaaMishra
    Icon for Super User rankSuper User
    2 years ago

    hi sebouier ,

    If my understanding is clear and below is the expected output for your problem statement : 


    Then please follow below steps : 

    • Create a summarize table : 
    • Create a many to many relationship with your originaltable and summarized table based on NAME column
    • Use the slicer for CAT from summarize table 
    • create the table visual by using columns from your originaltable (All the columns in table visual are coming from OriginalTable) 


      If this approach works for you, please select it as solution. 

    Thanks,

    Ankita

    • sebouier's avatar
      sebouier
      Frequent Visitor
      2 years ago

      Thanks for your quick help AnkitaaMishra . 

      Your approach is very elegant and smart.

       

      My approach was slightly different because I duplicated the Originaltable into Copytable. And I created a similar relation as yours: Originaltable.Name -> Copytable.Name

      Then I displayed on a visual table the fields from Copytable.

       

      But my real struggle is to be able to identify the filtered row in Originaltable.

      How would you proceed to add a column as a "filtered row flag"?

       

      Result expected : 

      IdNameCatfiltered row flag
      2Allanb1
      3Allanc0
      4Jasone0
      5Jasonf1
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi sebouier ,

    AnkitaaMishra Thanks for your reply!

    And sebouier , please try this way:
    First use this DAX to create a new table for slicer:

    Slicer = VALUES('Table'[Cat])

    Note: without any relationship!

    Then use this DAX to create a measure:

    Measure = 
VAR _Cat = VALUES(Slicer[Cat])
VAR _Name = MAX('Table'[Name])
RETURN
IF(
    CALCULATE(
        COUNTROWS(
            FILTER(
                'Table',
                'Table'[Name] = _Name && 'Table'[Cat] IN _Cat
            )
        ),
        ALLSELECTED('Table')
    ) > 0,
    1,
    0
)

    Set it up as shown in the following figure:

    Then use this DAX to create another measure:

    Flag = 
IF(
    MAX('Table'[Cat]) IN VALUES(Slicer[Cat]),
    1,
    0
)

    And put this measure into the table visual:


    The final output is as below:


    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • foodd's avatar
    foodd
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Please remember to adhere to the decorum of the Community Forum when asking a question.

    Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.

    https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150

    This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.