CODEWITHSUNDEEP
databasepowerbirelational-databasedaxpowerbi-desktop
Nick
Nick

Reputation: 55

How to filter a Line Chart with a Measure in Power BI?

I currently have a table in Power BI named Fruit.

Here is sample data from Fruit:

Issue id Label Label 1 Label 2 Label 3 Label 4 Created Resolved Time Difference (MINS)
1000 Apples Grapes Bananas Oranges Strawberries 14/03/2021 11:38:23 11/02/2022 525632
1001 Oranges Pears Apples Bananas Strawberries 13/03/2021 12:34:34 18/03/2022 11:38:23 524324
1002 Pears Dragon Fruit Apples Strawberries Dragon Fruit 04/03/2021 18:31:11 12/03/2022 11:38:23 525345
1003 Bananas Oranges Apples Grapes Pears 11/03/2021 19:34:57 11/03/2022 11:38:23 528264
1004 Grapes Apples Bananas Pears Strawberries 12/03/2021 12:32:52 15/03/2022 11:38:23 521927

I have created a table to join the label values into one: AllLabel = SUMMARIZE(UNION(VALUES(Fruit[Label 1]), VALUES(Fruit[Label 2]), VALUES(Fruit[Label 3]), VALUES(Fruit[Label 4])),Fruit[Label 1])

I also have created a measure count the labels and filter them uniquely: Apples Oranges Pears Bananas Strawberries Dragon Fruit Grapes:

SELECTEDFruit = 
var _selectedFruit = SELECTEDVALUE(AllLabel[Label 1])
return
CALCULATE(COUNTROWS(Fruit) , FILTER(Fruit,Fruit[Label 1] = _selectedFruit || Fruit[Label 2] = _selectedFruit || Fruit[Label 3] = _selectedFruit || Fruit[Label 4] = _selectedFruit))

This is how the tables look:

I have a line chart to calculate the Average resolution time: enter image description here enter image description here

However, when I choose a particular fruit using a slicer, it does not change the value of Average Resolution Time Line Chart. enter image description here

How can I filter the Average Resolution Line Chart using the Label 1 slicer?

Upvotes: 2

Views: 1880

Answers (1)

RF1991
RF1991

Reputation: 2265

the reason is that your tables don't have relationships. make sure that there exist relationships between tables. it can be one active relationship between two table,you should use dax Relationship functions such as USERELATIONSHIP formula for calculate between other columns. however i recommend using pivot,unpivot and union dax function like in order to create another table to get best result from slicer and there will be no need for your dax formula AllLabel

UnpivotedTable= FILTER(
UNION(
SELECTCOLUMNS('Fruit',"Created",[Created],"Resolved",[Resolved],"Time Difference (MINS)",[Time Difference (MINS)],"label",[Label]),
SELECTCOLUMNS('Fruit',"Created",[Created],"Resolved",[Resolved],"Time Difference (MINS)",[Time Difference (MINS)],"label",[Label 1]),
SELECTCOLUMNS('Fruit',"Created",[Created],"Resolved",[Resolved],"Time Difference (MINS)",[Time Difference (MINS)],"label",[Label 2]),
SELECTCOLUMNS('Fruit',"Created",[Created],"Resolved",[Resolved],"Time Difference (MINS)",[Time Difference (MINS)],"label",[Label 3]),
SELECTCOLUMNS('Fruit',"Created",[Created],"Resolved",[Resolved],"Time Difference (MINS)",[Time Difference (MINS)],"label",[Label 4])),[Label]<>"")

Upvotes: 1

Related Questions