Forum Discussion
Helper IIIHow to show duplicate records in Table visual in Direct Query mode
Hello experts,
I have below data in database. I am using Direct Query mode. When I build a table visual, it shows me only one record as the values are same in all 3 rows. I want to show all 3 rows in table visual. How to do this?
Data:
Name Customer Score Revenue
A Cust1 50 1000
A Cust1 50 1000
A Cust1 50 1000
Current o/p:
Name Customer Score Revenue
A Cust1 50 1000
Expected o/p:
Name Customer Score Revenue
A Cust1 50 1000
A Cust1 50 1000
A Cust1 50 1000
Thanks,
Anilesh
Hi, anileshknpowerb
The function to get a random number in Power BI is Rand() and Randsbetween(). However, they both don’t support Direct query mode. The step that adds a random column in the Power query also doesn’t support Direct query mode. Then I figure out a method that used the calculated table to achieve this, you can follow my steps:
- Create a calculated table:
Table = ADDCOLUMNS('Table_2',"Tag",RAND())- Create a table and place the columns in the new table, like this:
- Change the field color of [Tag] to white, as I did before:
And you can get what you want.
You can download my test pbix file here
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- amitchandak
Super User
anileshknpowerb , Create a new column
sum(Table[Revenue]) + Rand()
or
sumX(Table, Table[Revenue]) + Rand() )
and try. Other wise I doubt you can show.
You need add some unique column
- v-robertq-msft
Community Support
Hi, anileshknpowerb
According to your description, you want to make the three columns that are exactly the same to display their own value in the table chart through direct query mode, you can try my steps:
- Create a calculated column in the data table:
Tag = RAND()- Create a table chart and place columns like this, then set the summarize type of [Tag] to “Don’t summarize”:
- Then you can set it like this to make the [Tag] column disappear in your table chart:
And you can get what you want.
You can download my test pbix file here
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
anileshknpowerbI tried to create a DAX column 'Tag' Looks like,in Direct Query we can't create DAX column with value RAND()
Tag=RAND(). I get beloe error.
- v-robertq-msft
Hi, anileshknpowerb
The function to get a random number in Power BI is Rand() and Randsbetween(). However, they both don’t support Direct query mode. The step that adds a random column in the Power query also doesn’t support Direct query mode. Then I figure out a method that used the calculated table to achieve this, you can follow my steps:
- Create a calculated table:
Table = ADDCOLUMNS('Table_2',"Tag",RAND())- Create a table and place the columns in the new table, like this:
- Change the field color of [Tag] to white, as I did before:
And you can get what you want.
You can download my test pbix file here
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
