Need help with a measure

[admin_xlsior]

Hello everyone,

Need help or advice on createing some measure.

So I have this table:

Sales Order Id	ProcessDate
SO001	1/1/2019
SO001	1/2/2019
SO001	1/3/2019
SO001	1/4/2019
SO002	1/2/2019
SO002	1/3/2019
SO003	1/4/2019
SO004	2/1/2019
SO004	2/2/2019
SO004	2/3/2019
SO004	2/4/2019
SO004	2/5/2019
SO004	2/6/2019
SO004	2/7/2019
SO004	2/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,

[Anonymous]

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]

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,