Table calculation for Direct Query with historical data

[Anonymous]

I have two tables imported through Direct Query related by ID.

Table 1 contains details such as Status (Red, Green) and the Date when they appear on that status (red time, green time).

Table 2 has Serial no. for each ID.

I would like to show in a table visualization on a daily basis how many ID's are opened with Red status and how many turned Green. Note that some Red status ID's can remain on red status for a longer period.

So I would need to count how many unique ID's are on status Red and how many Serials are attributed to those for each day and when they are changed to Green.

Since the client wants this in real time and I have limited experience with Direct Query (and its limitations) I can't make any CALCULATE or New Columns where I can use it on a row by row basis.

Right now my data looks something like this:

After importing the data, ID & Date are recognised as Whole no. and Date types.

Can something like this be done with Direct Query option? I tried several tips that I found online (COUNTROWS, SUMX, VAR), the general rule I saw is that Measures are most commonly used in these situations, however it's not showing the same data as in the examples above or defaults to an error.

[Anonymous]

I'm going to try and show this as best I can through the reply function as I can't upload pbix or xlsx since I'm a new member:

TABLE 1

TABLE 2

What I would like to show is a table that shows in one visualization how many Red Id's/Serials are on a daily basis and how many Green Id's/Serials are, knowing that some Red Id's can turn green on the same day OR be resolved the following days:

The main issue here is that my data is through Direct Query and I saw that many DAX functions are not available so my data appears like this without summing the Id's:

Hope this better explains my inquiry!

Thank you!

[Anonymous]

Hi @Anonymous 

Can you provide detailed sample data file and the results you expect.So that I can help you better. Please remove any sensitive data in advance.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.