Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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.
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.
Solved! Go to 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.
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.
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 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.
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).
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.
It works for me! Thank you very much
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
87 | |
67 | |
49 |
User | Count |
---|---|
135 | |
113 | |
100 | |
68 | |
67 |