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 2 tables that do not have a relationship:
Date (1/1/2019 - 12/31/2025)
- date_key
- full_date
- EOM Month = FORMAT(DATE(1, MONTH('Date'[full_date]), 1), "mmmm")
Location (sample below)
- location_key
- start_date
- end_date
location sample data:
abc, 1/1/2020, 12/31/9999
lmn, 3/1/2022, 3/31/2022
xyz, 5/1/2022, 12/31/9999
My table should look like this:
EOMonth | Jan 2022 | Feb 2022 | Mar 2022 | Apr 2022 | May 2022 |
location count | 1 | 1 | 2 | 1 | 2 |
And it does because my measure is this:
But now, I want to filter based on the start_date and end_date, so if the user only wants to see locations that started between 3/1 and 5/1, abc will be filtered out and I'm left with:
EOMonth | Jan 2022 | Feb 2022 | Mar 2022 | Apr 2022 | May 2022 |
location count | 0 | 0 | 1 | 0 | 1 |
I'm not sure how to add my start_date filter to the above DAX and still maintain the ability to show count by month.
All help appreciated! Thanks!
Solved! Go to Solution.
I guess I just needed to write it out 🙂
I'm not sure if this is the best way, but I found that if I add a hidden filter to my table where location_key is not null, then it works.
I guess I just needed to write it out 🙂
I'm not sure if this is the best way, but I found that if I add a hidden filter to my table where location_key is not null, then it works.
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |