Creating new table in Direct query mode from existing one

[hari_krishnan]

Hi,

I have a Date range filter within my report which pulls data from the Reg_Date column from below table:

ID	Reg_Date	Gender	Age
1	2020-10-03 12:12:12	M	20
2	2020-11-03 12:12:12	M	20
3	2020-12-03 12:12:12	F	20

I also have a custom visual developed which needs data in the format below:

Table: stage_info_table

ID	Stage	Total Count
1	A	2
2	B	3

(Note: In real case I have 8 stages which gets total count based on different columns)

The total count is calculated by: 

    For Stage A: Count(*) from Table_Name Where Gender = 'M'

    For Stage B: Count(*) from Table_Name Where Age = '20'

What I did to generate this new table: Used SQL query to create this table. And the visual is working Perfectly based on the data.

The issue I am stuck with is:

1. The Data range filter will not work since no relation between the main table and created table.

2. A custom visual does not support more than 30K rows, so I cannot pass my main table to the custom visual.

Is there any way to create this new table in the Power BI itself?

Or Any other approach to solve this issue?

My thoughts: Only way to achieve this is with an API? Because The total count calculation to create new table is like a UNION of multiple queries which I think is very complex to achieve in BI.

What I need to achieve?

Data range slider changed --> Recalculates the Total count based on Reg_Date --> Pass new data to the custom visual

Thanks in advance.

[MFelix · Accepted solution]

Hi  @hari_krishnan ,

Have you tried instead of creating a new table, making a measure using a switch function and a table with the stages something similar to this:

Measure =
SWITCH (
    SELECTEDVALUE ( TableStage[Stage] );
    "A"; CALCULATE ( COUNT ( Table[Name] ); Table[Gender] = 'M' );
    "B"; CALCULATE ( COUNT ( Table[Name] ); Table[Age] = 20 );
    ...
)

Can work as you need, because the measure are based on context and if they are calculated based on the initial table they will be filtered out by the dates.

Be aware that the Stage only needs to have  A, B, C and so on without any relationship with the other table.

[hari_krishnan]

@MFelix Thanks a lot. This is working 😀

One more question, Does passing a measure works the same way as that of a column to the Custom Visual?

[MFelix]

Hi  @hari_krishnan ,

Not really sure if I understand what you mean by Custom Visual? 

In any visualization you can add in the values columns or measures the difference is that columns are part of your model and measures are calculated on request so when you use the measure it calculate.

[hari_krishnan]

Hi @MFelix ,

By Custom Visual, I mean custom created visuals using the "PbiViz" library. A package provided my Microsoft to create custom charts.

[MFelix]

Hi  @hari_krishnan ,

On visualizations as I refer you can use columns or measures so no problem in using them on custom visualizations, be aware that the only thing regarding measures and visualizations is that they can only be used on the values and on filters not for example on axis or legends.