The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Please help - I'm losing my mind.
I have 2 tables:
TABLE 1: Inventory, featuring
TABLE 2: Simply a calendar date table
They have an active relationship on the Start Date
I want to visualise, as a bar chart, the cost of everything in licence on any given day.
No amount of measures; relationships and calculated tables has gotten me there in the last 48 hours, and I might just have a breakdown. What am I not getting here? SO frustrating being a newb.
Thank you very much in advance!
Solved! Go to Solution.
OMG! I think I just got it 😄
OMG! I think I just got it 😄
Glad that you have solved this problem, you may accept the appropriate reply as a solution to close this topic and others will find it quickly. Thanks a lot.
Best Regards,
Community Support Team _ Jing Zhang
@mahoneypat well, this is incredible - I have removed the relationship, and now have a bar chart of number of active licences which gives correct results which is WAY closer than I've gotten so far, so thank you SO much on this, and for the reassurance. Definitely hadn't reached the passing variables stage yet.... clearly a lot to learn!
You mention changing the calculation to get total cost... which I'm trying, but am failing to get a working version of. Are you able to enlighten me any further? I've tried nesting another calculate and making various kinds of other measures etc. Sorry to be a pain, and thank you!!
Hi,
Create an Inactive relationship from License End Date to your Calendar date table. then you will need to create a DAX measure using CALCULATE and USERELATIONSHIP formulas to calculate the SUM of the deal cost per day. Add it to your bar shart.
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Hang in there. This is a little more advanced, so don't get too frustrated. Usually this is done with no relationship between the date and license table, with a measure like this. Put it in a visual with columns from your date table. This does the count of active licenses, but you can change the calculation to get total cost, etc.
Active Licenses =
VAR vMaxDate =
MAX ( 'Date'[Date] )
VAR vMinDate =
MIN ( 'Date'[Date] )
RETURN
CALCULATE (
COUNTROWS ( Inventory ),
ALLSELECTED ( Inventory[StartDate] ),
Inventory[StartDate] <= vMaxDate,
Inventory[EndDate] >= vMinDate
)
You won't need the ALLSELECTED part if there is no relationship between the tables, but if you do have it for other reasons, that will remove it for this calculation.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
95 | |
80 | |
62 | |
56 |
User | Count |
---|---|
252 | |
121 | |
112 | |
81 | |
70 |