Forum Discussion

Alan_GA's avatar
Alan_GA
Frequent Visitor
1 year ago
Solved

Filtering data by a visual is incorrect - Context issue

Hello, im new in power bi. I have a visual that shows a count of IDs for each status ("In process", "Pending", "Cancelled", etc.) using a measure, and below that, I have a table with an ID colum...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Alan_GA ,

     

    I think your issue is caused by that the [ID] column will be filtered by date. So it will only show ID in date range.

    Here I suggest you to create an unrelated ID table to help calculation.

    DimID = 
VALUES(Sheet1[ID])

    Measure:

    Measure = 
VAR _IDListbyDate =
    VALUES ( Sheet1[ID] )
VAR _FinishList =
    CALCULATETABLE ( VALUES ( Sheet1[ID] ), Sheet1[Status] = "Finished" )
VAR _InprocessList =
    CALCULATETABLE (
        VALUES ( Sheet1[ID] ),
        REMOVEFILTERS ( Sheet1[Date] ),
        Sheet1[Status] = "In process"
    )
RETURN
    IF (
        ISFILTERED ( Sheet1[Status] ),
        SWITCH (
            SELECTEDVALUE ( Sheet1[Status] ),
            "In process", IF ( MAX ( DimID[ID] ) IN _InprocessList, 1, 0 ),
            "Finished", IF ( MAX ( DimID[ID] ) IN _FinishList, 1, 0 )
        ),
        IF ( MAX ( DimID[ID] ) IN _IDListbyDate, 1, 0 )
    )

    You can create a table visual by this ID column, then add measure into visual level filter and set it to show items when value =1.

    Result is as below.

    By Default:

    Click on "In process"

    Click on "Finished"

     

    Best Regards,
    Rico Zhou

     

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

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
1 year ago

Alan_GA You probably need to use ALLEXCEPT or ALL to accomplish what you are attempting to do. Hard to say though without being able to play with some data. 

 

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

  • Alan_GA's avatar
    Alan_GA
    Frequent Visitor
    1 year ago

    https://drive.google.com/drive/folders/1tvGHQnUkekMXhAQfG0cbdrTI_XZvSStQ

    I just uploaded a .pbix file as an example. In it, you'll see a date slicer, a chart with two possible states. Additionally, there is a measure that performs a calculation based on these conditions (If the status is "In process", it should display the total number of cases regardless of the date. If the status is "Finished", it should display the total number of cases according to the date selected in the slicer).

    In the table below, I would need to see ALL the IDs that meet the criteria based on what is clicked in the bar chart.


    The visual table is filtering correctly (4 cases), but I need it to respect what is calculated in the measure (10 cases).

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Alan_GA ,

       

      I think your issue is caused by that the [ID] column will be filtered by date. So it will only show ID in date range.

      Here I suggest you to create an unrelated ID table to help calculation.

      DimID = 
VALUES(Sheet1[ID])

      Measure:

      Measure = 
VAR _IDListbyDate =
    VALUES ( Sheet1[ID] )
VAR _FinishList =
    CALCULATETABLE ( VALUES ( Sheet1[ID] ), Sheet1[Status] = "Finished" )
VAR _InprocessList =
    CALCULATETABLE (
        VALUES ( Sheet1[ID] ),
        REMOVEFILTERS ( Sheet1[Date] ),
        Sheet1[Status] = "In process"
    )
RETURN
    IF (
        ISFILTERED ( Sheet1[Status] ),
        SWITCH (
            SELECTEDVALUE ( Sheet1[Status] ),
            "In process", IF ( MAX ( DimID[ID] ) IN _InprocessList, 1, 0 ),
            "Finished", IF ( MAX ( DimID[ID] ) IN _FinishList, 1, 0 )
        ),
        IF ( MAX ( DimID[ID] ) IN _IDListbyDate, 1, 0 )
    )

      You can create a table visual by this ID column, then add measure into visual level filter and set it to show items when value =1.

      Result is as below.

      By Default:

      Click on "In process"

      Click on "Finished"

       

      Best Regards,
      Rico Zhou

       

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

       

      • Alan_GA's avatar
        Alan_GA
        Frequent Visitor
        1 year ago

        It works for me! Thank you very much