Forum Discussion

Graitec's avatar
Graitec
Regular Visitor
6 years ago
Solved

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
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    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

8 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

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

    • Graitec's avatar
      Graitec
      Regular Visitor
      6 years ago

      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. 

      • parry2k's avatar
        parry2k
        Super User
        6 years ago

        Graitec question comes down to , why you are it is as a calculated column, why not a measure which is usually the best approach?

    • Graitec's avatar
      Graitec
      Regular Visitor
      6 years ago

      Because I would like a sum value of licences for each week start date, can I do this with a measure?