cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Graitec
Regular Visitor

Visual filter from a DAX Sumx filter expression

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 - All = GENERATE ( CALENDAR(
DATE ( 2018, 1, 1 ),
TODAY()),
VAR currentDay = [Date]
VAR month = MONTH ( currentDay )
VAR year = YEAR ( currentDay )
RETURN ROW (
"MonthStartDate", DATE(year,month,01),"WeekStartDate",[Date]-weekday([Date],1)+1)
)

 

Dates - Week/Year Table

 

Dates - Week/Year = VALUES('Dates - All'[WeekStartDate])
 
I then add the following column to the Dates - Week/Year Table,
 
Number of Active Licences = Sumx(filter(Purchases,Purchases[License Expiry Date]<'Dates - Week/Year'[WeekStartDate] && Purchases[Count Filter]="True" && Purchases[Software Type]="Autodesk" && RELATED('Business Units'[Business Unit])="UK"),Purchases[Quantity])
 

 

It works prefectly however, as you can see in the DAX above, I have hard coded the Business Unit which is related the Purchases table to be UK.  So my question is how can I allow the user from the web version of this report to easily filter the licences by different Business Units?  Clearly the visual filter won't work because the filter is hard coded into the DAX.
 
I know I could do this using a parameter and a PBI template file but I don't want the users to have to use PBI desktop, I would like this to be done using the web interface only.
 
Any help would be really appreciated.
 
Thanks
1 ACCEPTED 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

View solution in original post

8 REPLIES 8
parry2k
Super User
Super User

@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.






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

View solution in original post

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?






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.





Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.