Create new query/table based on content of a column - part 2 - DirectQuery

[VegarOyfoss]

Hi all

This question has already been answerd before based on me importing the data from a SQLdatabase, and creating my own data model. However, since this table consists of 14.4 million rows and is growing with aprox 25-35000 rows daily, I would like to use DirectQuery instead, as this is much quicker

The problem:

"I have a table where one of the column specify a particular task (one our of four possibilities - Closed SCTasks, Closed Tickets, Touched Tickets and Effort Time). I would like to create 4 new tables based on the content of this coulmn called "Task". If it says Closed SCTasks, I want all of those rows copied into a new table/quiry where I only have Closed SCTasks/Effot Time and so on."

If I would have imported the data instead of directquery, I could use a FILTER expression to make a new table

NewTable1 =
FILTER ( Table1, Table1[Task] = "Closed SCTasks" )

Big thanks to @AlB  for giving me this solution

However, since using DirectQuery, it is not possible to create a new table. Is there any other way I can achieve the same or similar as above? Maybe using some kind of measure?

[Tahreem24]

Hi @VegarOyfoss ,

As calculated tables are not supported in DirectQuery Mode. Other alternative is to take 4 pages and apply Page level filter based on Task category individually.

Page 1 --> Page Level Filter as "Closed SCTasks"

Page 2 --> Page Level Filter as " Closed Tickets"

Page 3 --> Page Level Filter as "Touched Tickets"

Page 4 --> Page Level Filter as "Effort Time"

So, individual page contains only single TAsk category accordingly.

Don't forget to hit Thumbs up and accept this as a solution if you find it helpful! 

[VegarOyfoss]

Hi @Tahreem24 

Thanks for the suggestion. I have actually tried that. What I am trying to acheave is a dashboard where I have all KPIs on top in CARDS, and below I have a Column Chart with one KPI per agents and team (in this case only agents) as below:

And as you can see, when I click on one agent, all the KPIs change in the top CARDS accordingly

However, when I try this in DirectQuery, and I filter each card and column chart on each page, only the CARD on top according to the KPI in the Column Chart is reflected, the others are 'blank'.