Forum Discussion
Helper ICreating new table in Direct query mode from existing one
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.
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.
5 Replies
- MFelix
Super User
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_krishnanMFelix 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.