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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Reinhart
New Member

Caclulate the number each item is returning in a column, based on filtered report

dear all,

 

I created a separate table to calculate the number each item in a column is returning. This calculation is not taken into account the dynamical filters I use in the report. How can I implement the filters assigned by slicers towards this new table? 
Ex of the code of the table below 

IdenticalValuesTableBIS =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER (
                ALLSELECTED(Q_SLTL_Items),
                Q_SLTL_Items[Technisch object] <> BLANK()
            ),
            Q_SLTL_Items[Technisch object],
            "IdenticalCount", COUNTROWS(Q_SLTL_Items)
        ),
        "FilteredCount", 1
       
    )

main idea: 

the values which occur multiple times should be checked, but this depends on statuses of the object, type of use, ... --> I 'd like to have this calculation dynamically based on the assigned filters in PowerBI

Reinhart_0-1706859957985.png

 

Example of the report: 

Reinhart_2-1706860472192.png

 

 

2 REPLIES 2
123abc
Community Champion
Community Champion

To implement dynamic filtering in your Power BI report so that the calculations in your table are affected by slicers and other filters, you can use DAX functions such as CALCULATE and ALL to manipulate the filter context. Here's how you can modify your DAX expression to incorporate dynamic filtering:

 

IdenticalValuesTableBIS =
ADDCOLUMNS (
SUMMARIZE (
FILTER (
ALLSELECTED(Q_SLTL_Items),
Q_SLTL_Items[Technisch object] <> BLANK()
),
Q_SLTL_Items[Technisch object],
"IdenticalCount", CALCULATE(COUNTROWS(Q_SLTL_Items))
),
"FilteredCount", CALCULATE(COUNTROWS(Q_SLTL_Items))
)

 

In the above expression:

  • CALCULATE(COUNTROWS(Q_SLTL_Items)) is used within the SUMMARIZE function to calculate the count of rows in the filtered context.
  • ALLSELECTED(Q_SLTL_Items) ensures that all filters, including slicers and report-level filters, are considered in the context.
  • CALCULATE function is used again to ensure that the count of rows (COUNTROWS) is calculated within the proper filter context.

By using CALCULATE with COUNTROWS and ALLSELECTED, you are able to dynamically calculate the count of rows based on the filters applied in your report, including slicers and other filter elements.

 

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

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

dear 123abc, 

 

the solution did not work: 

in the table report, I created a 'tris' table to add the new function. The solution is not taken into account the filtered rows in the table based on the slicers.

Reinhart_1-1706869122425.png

 

thanks for your help

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.