Line graph with measure?

[Niklas_]

Hello, I'm relatively new to PowerBI, started using it this year, but so far have only made simple reports and due to covid I have not gotten any on-site courses yet which was planned but had to be canceled. So sorry in advance due to my lacking knowledge. It have mostly been self-study.

We have a proces for approving invoices, in this proces we want to check if the AP team is forwarding them to the correct person without the invoice being forwarded too many times.

Below is a sample of how the data is structured after some joins and filtering in SQL.

Table1

Row	Invoice	Status	Date	Employee
1	123	Created	01-01-2020	ap team
2	123	Approved	03-01-2020	AB
1	125	Created	05-01-2020	ap team
2	125	Approved	06-01-2020	CD
1	131	Created	06-01-2020	ap team
2	131	Approved	10-01-2020	EF
1	145	Created	21-01-2020	ap team
2	145	Forwarded	22-01-2020	GH
3	145	Forwarded	24-01-2020	ap team
4	145	Forwarded	27-01-2020	IJ
5	145	Forwarded	28-01-2020	ap team
6	145	Approved	01-02-2020	KL
1	148	Created	02-02-2020	ap team
2	148	Forwarded	05-02-2020	MN
3	148	Forwarded	09-02-2020	ap team
4	148	Forwarded	17-02-2020	OP
5	148	Forwarded	21-02-2020	ap team
6	148	Approved	25-02-2020	QR

To achieve my goal I've made a measure as shown below:

Control =
IF (
     (
        CALCULATE (
            DISTINCTCOUNT ( Table1[Row] ),
            FILTER ( Table1, Table1[Status] IN {"Created", "Approved"} && Table1[Employee] = "ap team" )
        )
    ) > 2,
    1,
    0
)

 The idea of this measure is to first check how many actions the AP team have taken, if they are forwarding it more than e.g. two times in this example it will show up as something that needs to be checked (Created counts as forwarding too). This measure only show the ones that needs to be checked, else everything is "OK".

I have a separate measure for the final approval date, which is basically just MAX(Date).

Output in a table with Invoice # shown:

Invoice	Final approval date	Control
123	03-01-2020	0
125	06-01-2020	0
131	10-01-2020	0
145	01-02-2020	1
148	25-02-2020	1

So far so good, now all I want is to put it in a line graph.

This is where I get to my question, I can't seem to put it in a line graph, probably due to my lacking knowledge of how the measures work in PowerBI.

Below is my output, it's from the live data so it's not the same as the table above, just a showcase of what I'm getting.

E.g. in the case from Table1 it should show 2 invoices to be checked in february and so on.

I feel like there's a very simple answer to this question, but I can't seem to make it work. Do I make a calculated column instead of measure or is there a simpler way to do this?

Any help would be appreciated.

Best regards

Niklas

[Niklas_]

@Greg_DecklerThank you, I went ahead and read your post, the generate function is not exactly what I'm looking for.

However aggregating the measures is exactly what I'm looking for and I actually used another answer from one of your previous answers: Grouping values from measures. I had completely forgotten about the summarize function, thank you.

As a side note Phil Seamark's book seems interesting, when I get further in my training I might take a look at it!

[Greg_Deckler · Accepted solution]

@Niklas_ - Well, seems like you would need to aggregate your Control measure to get 2 because if you just put your measure in your line graph it would return either 1 or 0 only for each month. Not sure which date your are using in your line graph, it should be the Final approval date I would think or another date from a calendar table that is related to that table. Might have a look at Open Tickets as it seems like you want something along the same lines as that. Open Tickets (powerbi.com)

[Niklas_]

Thank you for the quick reply, I will check it out!

Regarding the line graph you are right, forgot to put up a screenshot of it.

But yes, I do have a calendar table joined and I'm using the months from the calendar table.