Forum Discussion

admin_xlsior's avatar
admin_xlsior
Icon for Post Prodigy rankPost Prodigy
7 years ago

Need help with a measure

Hello everyone,

 

Need help or advice on createing some measure.

So I have this table:

Sales Order IdProcessDate
SO0011/1/2019
SO0011/2/2019
SO0011/3/2019
SO0011/4/2019
SO0021/2/2019
SO0021/3/2019
SO0031/4/2019
SO0042/1/2019
SO0042/2/2019
SO0042/3/2019
SO0042/4/2019
SO0042/5/2019
SO0042/6/2019
SO0042/7/2019
SO0042/8/2019

 

The mean of this table is, for each Sales Order, the proces date from Start to End, it will have their separate rows. For example on SO001, it started on 1 Jan 2019 and ended on 4 Jan 2019. So as you can see, there are 4 rows with continues dates for SO001.

 

And only based on that single table, if I want to create measure to know how many Sales Order are with Process Day more than 5 days, and how many with less than 5 days, what would the measure would be ?

 

If we do it manually, we can see there are 3 Sales Order with <5 days and 1 Sales Order (SO004) with >5 days.

 

Thanks in advance,

Tutorial Requests

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi admin_xlsior ,

     

    1. Create a new column 

    Category = IF(CALCULATE(COUNT('Table'[Sales Order Id]),ALLEXCEPT('Table','Table'[Sales Order Id]))>5,">5","<5")

    2. Add a visual (a chart, table, .. any kind you want your data to be displayed)

    3. Add the category and the count of "Sales Order Id" to the visual.

     

    Tell me if you have any problems ! 

     

    Regards, 

    Etienne

    • admin_xlsior's avatar
      admin_xlsior
      Icon for Post Prodigy rankPost Prodigy
      7 years ago

      Hi Ettiene,

       

      I cannot use CALCULATE in my new column. Maybe because I'm using "Direct query" ?

       

      Can we use Measure instead ?

       

      And forgot to mention, the visual I plan to use is Card or KPI.

       

       

      Thanks,