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

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?

    • v-deddai1-msft's avatar
      v-deddai1-msft
      Community Support
      6 years ago

      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

      • Graitec's avatar
        Graitec
        Regular Visitor
        6 years ago

        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])

         

         

  • 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?