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
Anonymous
Not applicable

Data filtering problem with slicer

Hi everyone,

I have a data table:

1.JPG

I need to perform KPI for Organization, Team and every employee, KPI of Team is the data of the employees in the team, KPI of Organization is the data of all employees. The result I want is a diagram showing 3 KPIs of the selected object.

 

2.JPG

To do this, I created a Hierachy Slicer to filter the objects that need to be calculated:

3.JPG

My slicer works fine if I create Measure to calculate each KPI value but it doesn't work when I create a new Data table to store KPI values, I don't know where the problem is, please help me.

9 REPLIES 9
v-lili6-msft
Community Support
Community Support

hi  @Anonymous 

you should know that:

  1. Calculation column/table not support dynamic changed based on filter or slicer.
  2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary in the visual.

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-lili6-msft ,

I know that with measure I can filter data by slicer, but the problem is how to display 3 values ​​'Number of Pass', 'Number of Fail' and 'Total' for each KPI as I wish.

 

2.JPG

With Measure list, I can only display them as adjacent columns:

 

1.png

 

hi  @Anonymous 

You need to keep a Kpi type into axis in the visual, here is a similar post for you refer to:

https://community.powerbi.com/t5/Desktop/Dynamic-average-on-X-axis-of-a-bar-chart/td-p/1083576

 

If you still have the problem, please share your sample pbix file and your expected output.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Dear @v-lili6-msft,

Can you please explain with an example.

This is the output I want to display:

 

Untitled.png

sample pbix file 

 

hi  @Anonymous 

Just try this way as below:

Step1:

Add a dim kpi type table, then use kpi type field in the bar visual

 

Step2:

Create another two measure as below:

numberpasskpi = SWITCH( SELECTEDVALUE('Table'[KPI Type]),
"KPI 1",[numberpasskpi1],
"KPI 2",[numberpasskpi2],
"KPI 3",[numberpasskpi3])
totalkpi = SWITCH( SELECTEDVALUE('Table'[KPI Type]),
"KPI 1",[totalkpi1],
"KPI 2",[totalkpi2],
"KPI 3",[totalkpi3])

Step3:

Then drag these two measure into visual

 

Result:

4.JPG

 

and here is sample pbix file, please try it.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-lili6-msft ,

I need to calculate the KPI Pass ratio, KPI Pass is a KPI with a value greater than or equal to the threshold:

2.JPG

To do this, I have added 1 "Threshold" column to compare with KPI value. How can I count the number of targets with a percentage greater than "Threshold". I find CALCULATE (COUNTROWS ()) unusable in this case

1.JPG

 

Anonymous
Not applicable

Dear @v-lili6-msft ,

Thank you very much for your enthusiastic help, my problem has been resolved.

Greg_Deckler
Super User
Super User

I may not be reading this correctly but it sounds like you are trying to create a dynamic table based upon user interaction. This simply is not possible. Data tables are calculated only at the time of data load (refresh).


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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi @Greg_Deckler, I just want to calculate based on the data loaded, every time Hierachy Slicer is selected, the value will change accordingly. I can do this on each Measure, but it doesn't work when I use a table to save the calculated KPI data.

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors