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,
I am trying to create a dashboard that contains data for each day in 15 minute steps.
I have a table with these 15 minute steps and another table with an end-date. The tables are related by the user ID
The problem is that these 15 minute steps exist for every day but I want to remove/don't show the data after the end-date.
(not every user has an end-date)
I hope this is understandable
Regards
Noah
Solved! Go to Solution.
Hi @noah44 ,
Only need adjust the measure can achive it.
1. my table:
Table 1:
Table 2:
2.the model is same as above
3.create a measure and add a table visual
Measure =
VAR cur_id =
SELECTEDVALUE ( Table1[ID] )
VAR cur_end_date =
SELECTEDVALUE ( Table1[End-Date] )
RETURN
SWITCH (
TRUE (),
ISBLANK ( cur_end_date ), SUM ( Table2[15 minute steps] ),
CALCULATE (
SUM ( Table2[15 minute steps] ),
FILTER ( ALL ( Table2 ), Table2[ID] = cur_id && Table2[Date] <= cur_end_date )
)
)
Please refer my attached .pbix file
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @noah44 ,
Please try below steps:
1. I have create two table for test
Table1:
Table2:
The model:
2. create a measure and add a table visual
Measure =
VAR cur_id =
SELECTEDVALUE ( Table1[ID] )
VAR cur_end_date =
SELECTEDVALUE ( Table1[End-Date] )
RETURN
CALCULATE (
SUM ( Table2[15 minute steps] ),
FILTER ( ALL ( Table2 ), Table2[ID] = cur_id && Table2[Date] <= cur_end_date )
)
If i misunderstand your demands, please feel free to let me know.
I have attached my .pbix file, please refer it.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-binbinyu-msft,
thanks for the response.
I am having a problem with all the IDs that have no end-date. There is no data shown for them.
Hi @noah44 ,
Only need adjust the measure can achive it.
1. my table:
Table 1:
Table 2:
2.the model is same as above
3.create a measure and add a table visual
Measure =
VAR cur_id =
SELECTEDVALUE ( Table1[ID] )
VAR cur_end_date =
SELECTEDVALUE ( Table1[End-Date] )
RETURN
SWITCH (
TRUE (),
ISBLANK ( cur_end_date ), SUM ( Table2[15 minute steps] ),
CALCULATE (
SUM ( Table2[15 minute steps] ),
FILTER ( ALL ( Table2 ), Table2[ID] = cur_id && Table2[Date] <= cur_end_date )
)
)
Please refer my attached .pbix file
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thank you so much for your help.
I changed the measure and now it shows all the data as it should
But it doesn't cut away the values that came after the end-date. I tried changing thetype from datetime to date, but the 15 minute steps always need to have datetime.
Regards
Noah
Hi @noah44 ,
In my example, I was able to get the correct result for my measure, because you didn't provide detailed data, so I can't tell what your problem is, please adjust it to your actual data according to the example I gave.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |