cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
alevandenes
Helper III
Helper III

UCL/LCL with filter

Good morning,

 

I have quite an interesting challenge that me and my colleagues cant solve. I hope someone here can help me.

I would like to add Upper Control Limit, Lower Control Limit and average line to a column chart.  The goal is to monitor specific issues on product and check when they are out of control.

 

I have managed to obtain what i need by creating a calculated table and mentioning already in there the filters i want to apply - see below:

 

Table 7 = SUMMARIZE('Date',[YearMonthnumber], "units", [units produced], "problems with product", CALCULATE(distinctCOUNT(problems with product[COMPLAINT_NUMBER]), problem with product[COMPLAINT_PHASE]="Closed", 'Countries to consider'[considered in:]="Yes", 'ModelstoInclude'[Consider]="Yes"))
 
 
alevandenes_0-1693397221373.png

 

 

However, this is not good enough for us because we want to be able to filter the graph by problem type. However, i am not able to specify the problem type already when i am creating the table cause it could be hundreds of different things. 
 
is there anyone that knows how i can filter by problem type my graph?
 
Kind regards
3 REPLIES 3
v-shex-msft
Community Support
Community Support

HI @alevandenes,

AFAIK, current power bi does not support create dynamic calculated column/table based on filter effects, they do not work on the same level and you can't use the child to affect its parent.

For this scenario, you may need to add 'problem type' as category to the summarize table with different results based on the 'problem type' group. Then you use filter on the result table based on problem type.

Notice: the data level of power bi(from parent to child level)

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi, thanks i understand. Is there maybe another way I can obtain this that it is not creating a table? this is the solution i found, but i am actually okay with changing everything completely if i can obtain what i need.

HI @alevandenes,

Perhaps you can try to create the basic summary table with all value result, then you can create measure expression to extract the total value and calculate with the percent of filter selection and total items.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

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

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors