How to show duplicate records in Table visual in Direct Query mode

[anileshknpowerb]

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

[v-robertq-msft · Accepted solution]

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:

1. Create a calculated table:

Table = ADDCOLUMNS('Table_2',"Tag",RAND())

2. Create a table and place the columns in the new table, like this:

3. 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.

[v-robertq-msft]

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:

1. Create a calculated column in the data table:

Tag = RAND()

2. Create a table chart and place columns like this, then set the summarize type of [Tag] to “Don’t summarize”:

3. 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.

[anileshknpowerb]

I 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.

[amitchandak]

@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