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.

     

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