Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Creating a multiple line chart report based on selections from disconnected slicers

Hi!

 

I have a report that I need to complete. I have succeeded to create a multiple line chart based on selections from two disconnected slicers, but the data in the line charts are wrong.

 

First some explaination of my case. Below you see a table with sampledata on fish. here you see a small part, but the line charts should show Avgweight on x axis and value on y axis from Table2. There can be a line chart per group and I can choose line charts from the disconnected slicer(s) which consist of a distinct list of group elements. I don't want to bother you explaining why the data seems a bit duplicated, but what I need is to pick one value, for instance for avgweight 959 for group K-factor you see three rows. I need to pick one of values, perhaps the one with min(index) which is 2821. Later I can add more slicers like cage, location, sampledate and so on, but the measure must work without using these. I hope you understand. A last thing I need to add to the measures a logic so they return blank if no elements in the Measurement1 slicer are selected. Just now all lines are shown if I have not selected any items. Below you see two of the measures I have started on. the first should calculate min values from the K-factor in the group column, and the next should calculate min values from the Pigment in the group column. I need to correct thes measures so I work as described above. the calculate part is of course wrong and must be replaced.

 

Measure 11 = IF("K-Faktor" IN VALUES('Y akse 1'[Measurement 1]); CALCULATE(SUM(Table2[Value]));BLANK())
Measure 13 = IF("Pigment" IN VALUES('Y akse 1'[Measurement 1]); CALCULATE(SUM(Table2[Index]) + 1000);BLANK())
 
I hope somebody can give me a hint on how to solve this issue
Regards Geir F

 

AvgWeightLocationCageSampleDateGroupValueIndex
902Danielsvika0226.02.2019 15:00:00Pigment3,962981
902Danielsvika0226.02.2019 15:00:00K-Faktor1,23682971
959Store KufjordM210.03.2019 15:00:00K-Faktor1,02082836
959Store KufjordM210.03.2019 15:00:00Pigment5,892846
959Store KufjordM310.03.2019 15:00:00K-Faktor1,02082851
959Store KufjordM210.03.2019 15:00:00K-Faktor1,07142821
959Store KufjordM210.03.2019 15:00:00Pigment5,112831
959Store KufjordM310.03.2019 15:00:00Pigment5,892861
1048Brensholmen 3735705 (TD)26.03.2019 10:41:22K-Faktor1,1582446
1048Brensholmen 3735705 (TD)26.03.2019 10:41:22Pigment3,9714285712456
1067NæringsbuktaN1 (D)01.09.2019 13:19:28K-Faktor1,291276
1067NæringsbuktaN1 (D)01.09.2019 13:19:28Pigment5,081286
1072Store KufjordM310.03.2019 15:00:00Pigment4,812876
1072Store KufjordM310.03.2019 15:00:00K-Faktor1,0842866
1136Danielsvika1205.09.2019 15:00:00K-Faktor1,2047991
1136Danielsvika1205.09.2019 15:00:00Pigment4,91001
1136Danielsvika0705.09.2019 15:00:00K-Faktor1,20471171
1136Danielsvika0705.09.2019 15:00:00Pigment4,91181
1136Danielsvika0305.09.2019 15:00:00Pigment4,91106
1136Danielsvika0305.09.2019 15:00:00K-Faktor1,20471096
1136Danielsvika0205.09.2019 15:00:00K-Faktor1,20471036
1136Danielsvika0205.09.2019 15:00:00Pigment4,91046
1182Brensholmen 3735707 (TD)26.03.2019 10:41:22K-Faktor1,23372461
1182Brensholmen 3735707 (TD)26.03.2019 10:41:22Pigment4,2952380952471
1216Store Kobbøya15-04-C19.03.2019 15:00:00Pigment4,742711
1216Store Kobbøya01-21-A19.03.2019 15:00:00K-Faktor1,16282671
1216Store Kobbøya09-D19.03.2019 15:00:00K-Faktor1,16282686
1216Store Kobbøya01-21-A19.03.2019 15:00:00Pigment4,742681
1216Store Kobbøya05-19-M19.03.2019 15:00:00K-Faktor1,16282596
1216Store Kobbøya15-04-C19.03.2019 15:00:00K-Faktor1,16282701
1216Store Kobbøya09-D19.03.2019 15:00:00Pigment4,742696
1216Store Kobbøya08-B19.03.2019 15:00:00Pigment4,742591
1216Store Kobbøya08-B19.03.2019 15:00:00K-Faktor1,16282581
1216Store Kobbøya05-19-M19.03.2019 15:00:00Pigment4,742606
1286No LocationNo Cage16.05.2019 15:00:00Pigment4,232216
1286No LocationNo Cage16.05.2019 15:00:00K-Faktor1,10772206
1326Store Kvalfjord05-0617.04.2019 15:00:00K-Faktor1,15032341
1326Store Kvalfjord05-0617.04.2019 15:00:00Pigment5,122351
1326Store Kvalfjord01-0217.04.2019 15:00:00Pigment5,122336
1326Store Kvalfjord01-0217.04.2019 15:00:00K-Faktor1,15032326

 

2 Replies

  • dax's avatar
    dax
    Community Support

    Hi  Anonymous ,

    I am not sure whether this is hwta you want, you could refer to my sample for details. If this is not what you want, please correct me and inform me more detailed information.

    Best Regards,
    Zoe Zhi

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Actually it should do an union of both slicers. But the most important thing to solve for me is the dax code

       

      I want to have data for line chart based on group column. I have to use Avgweight on the x axis and Value on y axis. But before that I need to fix my dax measure so I have only one value per group and per avg weight. Look below the avg weights of 959 for K-Faktor have 3 values. The dax code should pick one of them, either min or max. Doing so we can use min index or value of couse. I thought to create several measures like this one per group item. Hope you can help

       

      Measure 12 =
      VAR myValue = IF("Fett" IN VALUES('Y akse 1'[Measurement 1]);
      MINX (
      FILTER (
      SELECTCOLUMNS (
      FILTER ( Table2; Table2[Group] = "Fett" );
      "index"; Table2[Index];
      "Min"; MINX (
      FILTER ( Table2; Table2[Group] = EARLIER ( Table2[Group] ) );
      Table2[Value]
      )
      );
      [index] = MAX ( Table2[Index] )
      );
      [Min]
      );BLANK()
      )
      RETURN myValue

       

      AvgWeightLocationCageSampleDateGroupValueIndex
      902Danielsvika0226.02.2019 15:00:00K-Faktor1,236818
      902Danielsvika0226.02.2019 15:00:00Fett9,0219
      959Store KufjordM310.03.2019 15:00:00Fett7,2621
      959Store KufjordM310.03.2019 15:00:00K-Faktor1,020822
      959Store KufjordM210.03.2019 15:00:00Fett7,2624
      959Store KufjordM210.03.2019 15:00:00K-Faktor1,020825
      959Store KufjordM210.03.2019 15:00:00Fett5,8627
      959Store KufjordM210.03.2019 15:00:00K-Faktor1,071428
      1048Brensholmen 3735705 (TD)26.03.2019 10:41:22K-Faktor1,15833
      1048Brensholmen 3735705 (TD)26.03.2019 10:41:22Fett11,3142857134
      1067NæringsbuktaN1 (D)01.09.2019 13:19:28Fett8,1637
      1067NæringsbuktaN1 (D)01.09.2019 13:19:28K-Faktor1,2938
      1072Store KufjordM310.03.2019 15:00:00K-Faktor1,08439
      1072Store KufjordM310.03.2019 15:00:00Fett6,9641
      1136Danielsvika1205.09.2019 15:00:00Fett8,4942
      1136Danielsvika0705.09.2019 15:00:00K-Faktor1,204745
      1136Danielsvika0305.09.2019 15:00:00Fett8,4947
      1136Danielsvika0305.09.2019 15:00:00K-Faktor1,204748
      1136Danielsvika0205.09.2019 15:00:00Fett8,4950
      1136Danielsvika0705.09.2019 15:00:00Fett8,4951
      1136Danielsvika0205.09.2019 15:00:00K-Faktor1,204752
      1136Danielsvika1205.09.2019 15:00:00K-Faktor1,204753
      1182Brensholmen 3735707 (TD)26.03.2019 10:41:22K-Faktor1,233754
      1182Brensholmen 3735707 (TD)26.03.2019 10:41:22Fett13,8714285755
      1216Store Kobbøya09-D19.03.2019 15:00:00K-Faktor1,162858
      1216Store Kobbøya09-D19.03.2019 15:00:00Fett9,6359
      1216Store Kobbøya15-04-C19.03.2019 15:00:00K-Faktor1,162861
      1216Store Kobbøya01-21-A19.03.2019 15:00:00Fett9,6362
      1216Store Kobbøya15-04-C19.03.2019 15:00:00Fett9,6363
      1216Store Kobbøya08-B19.03.2019 15:00:00Fett9,6364
      1216Store Kobbøya05-19-M19.03.2019 15:00:00K-Faktor1,162866
      1216Store Kobbøya05-19-M19.03.2019 15:00:00Fett9,6367
      1216Store Kobbøya08-B19.03.2019 15:00:00K-Faktor1,162870
      1216Store Kobbøya01-21-A19.03.2019 15:00:00K-Faktor1,162871
      1286No LocationNo Cage16.05.2019 15:00:00Fett7,372
      1286No LocationNo Cage16.05.2019 15:00:00K-Faktor1,107773
      1326Store Kvalfjord01-0217.04.2019 15:00:00K-Faktor1,150376
      1326Store Kvalfjord01-0217.04.2019 15:00:00Fett9,3177
      1326Store Kvalfjord05-0617.04.2019 15:00:00K-Faktor1,150378
      1326Store Kvalfjord05-0617.04.2019 15:00:00Fett9,3179