Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Thanks a lot in advance for any hint!!!
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,
Thanks @v-sihou-msft for your suggestions.
No problem with point 1, I've understood
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
Thanks a lot in advance!
Any suggestion?
By right it should be working. Can you share some sample data to check further.
Cheers
CheenuSing
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |