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 All,
Brand new to Power BI but used to use Qlik.
We have a a list of users and their Billing Cycles ( And success Status) in one fact table and a Master calendar in the next. We are using Direct Query.
I want to be able to select a date or use any attributes from the Master Calendar and see all the billing cycles that date is within.
so if i select a day, week, hour month etc i see all the billing cycles where that day, week, hour, month etc is between the cycle startdate and end date.
I'm not sure if this is DAX or a relationship thing but would really apprecaite some help.
Thanks people 🙂
Alex
Solved! Go to Solution.
Please try this approach to get your desired result. I wasn't sure if you have a relationship between the tables, but this should work either way. It should count the cycles that overlap with the Date range in scope in your visual (Day, Month, etc.).
Cycles =
VAR __mindate =
MIN ( 'Date'[Date] )
VAR __maxdate =
MAX ( 'Date'[Date] )
RETURN
CALCULATE (
COUNTROWS ( Cycles ),
ALL ( 'Date' ),
Cycles[StartTime] <= __maxdate,
Cycles[StopTime] >= __mindate
)
.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Please try this approach to get your desired result. I wasn't sure if you have a relationship between the tables, but this should work either way. It should count the cycles that overlap with the Date range in scope in your visual (Day, Month, etc.).
Cycles =
VAR __mindate =
MIN ( 'Date'[Date] )
VAR __maxdate =
MAX ( 'Date'[Date] )
RETURN
CALCULATE (
COUNTROWS ( Cycles ),
ALL ( 'Date' ),
Cycles[StartTime] <= __maxdate,
Cycles[StopTime] >= __mindate
)
.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Will this still work if say the chart has a heirachy and the uses changes from dates to month and there is no longer any filtration on the Date field?
Thanks,
Alex
@Alex1988 , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
in direct query mostly measures will work, so need check some sample data
Hello ! Thank you for the response.
So on the left is the billing cycles fact table and on the right a pretty typical Calendar table. When i use date, week, month, year etc in my visualisation - i want Power BI to filter to all the billing cycles ranges where the date is between the start and end date.
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 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |