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.
I have created a report that would filter based on three date ranges as below
DateFilterRange = IF(MAX(vw_patientListNew[StatusDate]) in UNION(VALUES(DateRange1[Date]),VALUES(DateRange2[Date]),VALUES(DateRange3[Date])),1)
Now i want to summarize it like the table below
TimeFrameDateRangeCol1 | TimeFrameDateRangeCol2 | TimeFrameDateRangeCol3 | |
Interventional TX | 23 | 65 | 12 |
Interventional Non TX | 1 | 34 | 19 |
TimeFrameDateRangeCol1 =
IF(MAX(vw_patientListNew[StatusDate]) in VALUES(DateRange1[Date]),1)
TimeFrameDateRangeCol2 =
IF(MAX(vw_patientListNew[StatusDate]) in VALUES(DateRang2[Date]),1)
TimeFrameDateRangeCol3 =
IF(MAX(vw_patientListNew[StatusDate]) in VALUES(DateRange3[Date]),1)
TimeFrameDateRangeCol1, TimeFrameDateRangeCol2, TimeFrameDateRangeCol3 are calculation column and should return 1 when the statusdate intersect the respective filter. As you can see i am not able to achieve with the Daxcode.
Once i get the values for the each collumn TimeFrameDateRangeCol1, TimeFrameDateRangeCol2, TimeFrameDateRangeCol3 i can summarize TimeFrameDateRangeCol1, TimeFrameDateRangeCol2, TimeFrameDateRangeCol3 to create a summary table.
Is there another solution to this?
Hi @Anonymous,
Are you able to provide some sample data and what the expected outcome would be. One thing to note is that calcuated columns do not evaluate in the context of any filters or slicers applied to the report, they are calcluated when the model is processed, the values are then locked in place. Measures on the other hand are evaluated when the model is queried by the report.
Hope that makes sense
Proud to be a Super User!
@Anonymous , three slicers should be coming from three date tables or three-column. You might have ignore the other when one is working.
refer to this example for two date ranges
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
As you can see above the slicer data is coming from three different tables.
@Anonymous not sure how tables are connected like slicers are from disconnected tables, you have to add your calculation as measures, not columns
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
These are the tables from where slicer data is coming.
I have added the calculation measure and i cannot summarize the data. I can only summarize the data with a column.
My goal here is to create a summarized table as shown in my post.
Hi @Anonymous,
If you can provide the .pbix file I am pretty sure that @parry2k , @amitchandak or myself can help you with the measure you are looking for.
Thanks,
Richard
Proud to be a Super User!
The site doesn't let me upload file.
Data:
StatusDate | InterventionType |
7/21/1989 0:00 | Interventional TX |
1/8/1990 0:00 | Interventional TX |
4/30/1990 0:00 | Interventional TX |
11/3/2006 0:00 | Interventional Non TX |
11/6/2006 0:00 | Interventional TX |
11/8/2006 0:00 | Interventional Non TX |
11/9/2006 0:00 | Interventional TX |
11/13/2006 0:00 | Interventional TX |
11/14/2006 0:00 | Interventional Non TX |
11/14/2006 0:00 | Interventional TX |
11/15/2006 0:00 | Interventional Non TX |
11/16/2006 0:00 | Interventional TX |
11/17/2006 0:00 | Interventional Non TX |
11/20/2006 0:00 | Interventional TX |
11/22/2006 0:00 | Interventional Non TX |
11/22/2006 0:00 | Interventional TX |
11/27/2006 0:00 | Interventional TX |
11/28/2006 0:00 | Interventional Non TX |
11/28/2006 0:00 | Interventional TX |
11/30/2006 0:00 | Interventional Non TX |
11/30/2006 0:00 | Interventional TX |
12/1/2006 0:00 | Interventional Non TX |
12/1/2006 0:00 | Interventional TX |
12/4/2006 0:00 | Interventional TX |
12/5/2006 0:00 | Interventional TX |
12/6/2006 0:00 | Interventional TX |
12/7/2006 0:00 | Interventional TX |
12/11/2006 0:00 | Interventional Non TX |
12/11/2006 0:00 | Interventional TX |
12/12/2006 0:00 | Interventional Non TX |
12/12/2006 0:00 | Interventional TX |
12/13/2006 0:00 | Interventional Non TX |
12/14/2006 0:00 | Interventional TX |
12/15/2006 0:00 | Interventional Non TX |
12/18/2006 0:00 | Interventional Non TX |
12/19/2006 0:00 | Interventional Non TX |
12/19/2006 0:00 | Interventional TX |
12/20/2006 0:00 | Interventional Non TX |
12/20/2006 0:00 | Interventional TX |
12/21/2006 0:00 | Interventional Non TX |
12/21/2006 0:00 | Interventional TX |
12/22/2006 0:00 | Interventional Non TX |
12/27/2006 0:00 | Interventional TX |
12/28/2006 0:00 | Interventional TX |
1/2/2007 0:00 | Interventional Non TX |
1/4/2007 0:00 | Interventional TX |
1/5/2007 0:00 | Interventional Non TX |
2/24/2020 0:00 | Interventional Non TX |
2/24/2020 0:00 | Interventional TX |
2/25/2020 0:00 | Interventional Non TX |
2/25/2020 0:00 | Interventional TX |
2/26/2020 0:00 | Interventional Non TX |
2/26/2020 0:00 | Interventional TX |
2/27/2020 0:00 | Interventional Non TX |
2/27/2020 0:00 | Interventional TX |
2/28/2020 0:00 | Interventional Non TX |
2/28/2020 0:00 | Interventional TX |
3/1/2020 0:00 | Interventional Non TX |
Desired Results :
Slicer1(1/1/1989 - 7/26/1990) | Slicer2(11/3/2006 - 1/5/2007) | Slicer3(2/24/2020-3/1/2020) | |
Interventional TX | 3 | 24 | 5 |
Interventional Non TX | 0 | 20 | 6 |
I have three slicers:
Hi @Anonymous
Please have a look at the .pbix file created.
I contains 4 measures:
Slicer 1 Value = CALCULATE(COUNTROWS('Table'), 'Table'[StatusDate] in VALUES('slicer 1'[Date]))
Slicer 2 Value = CALCULATE(COUNTROWS('Table'), 'Table'[StatusDate] in VALUES('slicer 2'[Date]))
Slicer 3 Value = CALCULATE(COUNTROWS('Table'), 'Table'[StatusDate] in VALUES('slicer 3'[Date]))
Total Slicer Value Selection = [Slicer 1 Value] + [Slicer 2 Value] + [Slicer 3 Value]
which evaluate to the values you are looking for
Hope this helps
Proud to be a Super User!
@richbenmintz Yes this the result I was expecting but when I click on the slicer 2 value and "Interventional Non TX" which has 7 counts should only display 7 but display the entire row count 13. Is there a way to display only 7 in detail report? should I use a matrix ? please suggest
Hi @Anonymous,
Not really sure what you mean selecting the value in the table created it does not filter the table, what detail visual are you referring to
Proud to be a Super User!
Hi @richbenmintz i am referring to another table visual I added that contains a list of statusdate and intervention type
Hi @Anonymous,
Given that your date dimensions are not connected to the fact table, you will need to include the measure in your detail visual.
Proud to be a Super User!
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |