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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Alan_GA
Frequent Visitor

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 column.
When I filter by a certain date and click on any status, I want to see the detailed list of IDs for that status.

The problem is that when I click on 'Pending', I want to keep all of my 'Pending' ID values and display all of them in the table, ignoring the date filter.
However, when I click on 'Finished', I want the IDs to be filtered based on the selected date.


Clicking on 'Pendiente' shows only two values, but I want to display all 58 values (ignoring the date filter). The two values currently shown are filtered by the selected date, which is fine, but I still need to see the same 58 values in the table.

Alan_GA_0-1727877035598.png

Measure used in my visual (The measure shows the correct values, but the IDs in the table do not match

 

 

Q proyectos = IF(SELECTEDVALUE(Table[Estado_General])="Finalizado",COUNTROWS(Table),

        IF(SELECTEDVALUE(Table[Estado_General])="En Proceso", 
            CALCULATE(COUNTROWS(Table),ALL(Table[Date])),
         
                IF(SELECTEDVALUE(Table[Estado_General])="Pendiente", 
                    CALCULATE(COUNTROWS(Table),ALL(Table[Date])),

                    IF(SELECTEDVALUE(Table[Estado_General])="Standby", 
                    CALCULATE(COUNTROWS(Table),ALL(Table[Date])),
                        COUNTROWS(Table)
                  )
          )   

    )
        )
    )
        )
          )   

    )
        )

 

 

 

Sorry for my english. 

I hope you can help me. Thanks.

1 ACCEPTED SOLUTION

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.

vrzhoumsft_0-1727935517706.png

Result is as below.

By Default:

vrzhoumsft_1-1727935536020.png

Click on "In process"

vrzhoumsft_2-1727935552121.png

Click on "Finished"

vrzhoumsft_3-1727935569366.png

 

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.

 

View solution in original post

6 REPLIES 6
AlexisOlson
Super User
Super User

How about just turning off the interaction between the date slicer and the ID table?

 

Change how visuals interact in a report - Power BI | Microsoft Learn

Hi, thanks for your reply. If I turn off interaction, I get the correct results for the IDs in "Pending" status. However, when I click on my "Finished" bar, the displayed information is incorrect because it needs to be filtered by date.

The IDs in "Pending" status should be taken entirely regardless of the date, while the "Finished" ones should be within a specific date range

Alan_GA_0-1727892996123.png

 

Greg_Deckler
Super User
Super User

@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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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.

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

 

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.

vrzhoumsft_0-1727935517706.png

Result is as below.

By Default:

vrzhoumsft_1-1727935536020.png

Click on "In process"

vrzhoumsft_2-1727935552121.png

Click on "Finished"

vrzhoumsft_3-1727935569366.png

 

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.

 

It works for me! Thank you very much

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.