cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

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,

2 REPLIES 2
Highlighted
Resolver III
Resolver III

Re: Need help with a measure

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

Highlighted
Post Patron
Post Patron

Re: Need help with a measure

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,

 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors