Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I want to query the number of licences which are active for each week so I can show the increase in licences over time. I have created two date tables as below which work, I suspect there is a better way of doing this but I'm quite new to PBI.
Dates - All Table
Dates - Week/Year Table
Solved! Go to Solution.
Hi @Graitec ,
Would you please refer to the measure below:
Number of Active Licences = Sumx(filter(Purchases,Purchases[License Expiry Date]<MAX('Dates - Week/Year'[WeekStartDate])),Purchases[Quantity])
Best Regards,
Dedmon Dai
@Graitec seems like Business Unit is a separate table if you add that as a slicer and remove it from your measure, so based on the selected value from the slicer, it will filter the measure. Not sure if I'm missing something here, I don't see why you have fixed value of UK in the measure.
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.
Because I would like a sum value of licences for each week start date, can I do this with a measure?
Thank you for replying.
Yes Business Unit is a related table however the count DAX isn't a measure, it's a column because I want to reply on the count for each week in the past. From what I understand a slicer won't work on a column like this as I need to include the filter in the DAX.
Hi @Graitec ,
I also recommend that you use measure and then use slicer for filter. Because the calculated column is fixed, it is recalculated only when the data is refreshed.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Could you help me create the measure that would give me the same data as the column I currently have. Basically what I want is a count for each week of the number of licence on that date, so it would have to query the date of each week and use that date in the DAX to come up with with the count of licences. This is why I thought I couldn't do this in a measure because it need to be done for each row of the table.
Below is the current column calculation, I've simplified a bit further just to hopefully explain what I'm trying to achieve, the part in bold is the data from that row of the table, can this be done using a measure?
Number of Active Licences = Sumx(filter(Purchases,Purchases[License Expiry Date]<'Dates - Week/Year'[WeekStartDate]),Purchases[Quantity])
Hi @Graitec ,
Would you please refer to the measure below:
Number of Active Licences = Sumx(filter(Purchases,Purchases[License Expiry Date]<MAX('Dates - Week/Year'[WeekStartDate])),Purchases[Quantity])
Best Regards,
Dedmon Dai
This worked perfectly, thanks very much Dedmon!
@Graitec question comes down to , why you are it is as a calculated column, why not a measure which is usually the best approach?
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |