Forum Discussion

laciodrom_80's avatar
laciodrom_80
Icon for Helper IV rankHelper IV
9 years ago

Help with DAX measure for chart

Hi guys,

 

I've got a slicer containing time-series names and a chart in which I display the curve trend of the selected serie: the slicer is populated with the column SerieName of Table1

 

 

 

Table1
ID      SerieName                    From_Date
 1       Serie1               14/5/2016 16:55 00:00:00
 2       Serie2               22/7/2016 06:05 00:00:00
....

 

 

The time-series values I use to create the chart are contained into Table2

 

 

Table2
Serie_Id               Value                    Date
 1                     5,63         01/05/2016 00:00 00:00:00
 1                     10,5         01/05/2016 01:00 00:00:00
 1                     13,01        01/05/2016 02:00 00:00:00
....
 2                     1,43         01/05/2016 00:00 00:00:00
 2                     1,5          01/05/2016 01:00 00:00:00
 2                     2,01         01/05/2016 02:00 00:00:00

 

This is the measure I use to draw the line-chart (x-axis contains Date column of Table2)

 

 

Serie Value = IF(HASONEVALUE('Table1'[SerieName]);CALCULATE(Sum('Table2'[Value]));BLANK())

 

Two questions:

 

  1.  I use IF and HASONEVALUE statements in Serie Value measure because I would like to display the chart only when a serie name is selected in the slicer: it works correctly if there are more than 1 serie name into the slicer, but if there is only one serie in the slicer the chart is displayed even if the serie isn't selected. How can correct this?
  2. How can modify the measure to display the chart only for dateTime > selectedDateSerie (selectedDateSerie is the corresponding From_Date of the selected serie name into the slicer)

 

Thanks a lot in advance for any hint!!! :smileywink:

Need Help

4 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    laciodrom_80

     

    1. If you don't want to show any values when there's no selection in slicer, you should use ISFILTERED() in your condition. 

    Serie Value = IF(HASONEVALUE('Table1'[SerieName]) && ISFILTERED('Table1'[SerieName]) ;CALCULATE(Sum('Table2'[Value]));BLANK())

    See my test below:

     

     

     

    2. If you have built the relationship between two tables (one to many on Series ID), you can directly use the From_Date column in your IF statement.

     

    Serie Value = IF(HASONEVALUE('Table1'[From_Date]) && ISFILTERED('Table1'[From_Date]) ;CALCULATE(Sum('Table2'[Value]));BLANK())

    Regards,

     

    • laciodrom_80's avatar
      laciodrom_80
      Icon for Helper IV rankHelper IV
      9 years ago

      Thanks v-sihou-msft for your suggestions.

       

      No problem with point 1, I've understood :smileywink:

       

      About point 2, yes I've got a One to many relationship between the two tables (one to many on Series ID), perhaps I didn't explain it well:

      After selecting a serie from the Series Names filter, I'd like to display into my chart only value of datetime > selectedDate (where for selectedDate I mean the corresponding From_Date date of the selected Serie Name in the filter. I've almost reached the aim with this DAX measure, but I have used the "NOW()" date instead of the selectedDate

       

      Serie Value = IF(HASONEVALUE('Table1'[SerieName]) && ISFILTERED('Table1'[SerieName]);CALCULATE(SUMX(FILTER(Table2; [DateTime]>NOW()); 'Table2'[Value]));BLANK())

      How should I modify the above expression? If I try to substitute "NOW()" by Table1[From_Date] I've got an error :manfrustrated:

       

       

      Thanks a lot in advance!

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi laciodrom_80

     

    By right it should be working.  Can you share some sample data to check further.

     

    Cheers

     

    CheenuSing