hi

[abc_777]

Hello,

i have a very silly issue 

i want to see following both line of entry in power bi

if i filter by BG0000117 then i can see only that line of entry but i dont want to use filter for BG0000117

just simple wnat to see both line in visual

thanks

[ryan_mayu · Accepted solution]

@abc_777 

pls see the attachment below

[ryan_mayu · Accepted solution]

after you move columns and null value, then you need to remove duplicates.

[ryan_mayu]

maybe you can try to create a dim table which contains two columns

BG0000117 and data in first column

then you create relationship between two tables. 

when you filter BG000017 in the dim table. you can see two lines of your original table

[abc_777]

Hello

Thanks for your reply. would mind to help me with a pbi file with this sample data 

INVOICE_NO	INVOICE_DT	USER_BARCODE	Retail_Price	SQTY	DISC_TYPE	Return	Amount	Store
223050600020	06-05-23	36002	214	1		0	214	100010049
223050600020	06-05-23	36002	0	1	BG0000117	0	0	100010049
CR02230500010	06-05-23	36002	214	0		1	0	100010049
223050600022	06-05-23	36002	214	1		0	214	100010049
223050600022	06-05-23	36002	0	1	BG0000117	0	0	100010049
223051000002	10-05-23	36002	292.29	10		0	2922.9	100010049
CR02230500019	10-05-23	36002	292.29	0		10	0	100010049
223051000004	10-05-23	36002	292.29	10		0	2922.9	100010049
323051000004	10-05-23	36002	292.29	8		0	2338.32	100010049
223051000054	10-05-23	36002	213.75	12		0	2565	100010049
223051000067	10-05-23	36002	292.29	8		0	2338.32	100010049
223051000071	10-05-23	36002	236.25	6		0	1417.5	100010049
223051000086	10-05-23	36002	213.75	6		0	1282.5	100010049
223051000089	10-05-23	36002	214	1		0	214	100010049
223051000089	10-05-23	36002	0	1	BG0000117	0	0	100010049
323051100011	11-05-23	36002	292.29	8		0	2338.32	100010049
223051100068	11-05-23	36002	236.25	3		0	708.75	100010049
223051100084	11-05-23	36002	214	1		0	214	100010049
223051100084	11-05-23	36002	0	1	BG0000117	0	0	100010049
223051500088	15-05-23	36002	273.92	1		0	0	100010049
423051500005	15-05-23	36002	214	3		0	642	100010049
423051500005	15-05-23	36002	0	3	BG0000117	0	0	100010049
223051700023	17-05-23	36002	236.25	12		0	2835	100010049
323051700019	17-05-23	36002	0	1	BG0000117	0	0	100010049

Thanks

[ryan_mayu]

what's the expected output?

do you want to show below invoice?

223050600020

223050600022

223051000089

223051100084

423051500005

323051700019

[abc_777]

yes

[ryan_mayu · Accepted solution]

@abc_777 

pls see the attachment below

[abc_777]

soo quick response and perfect. thanks buddy

one quick think here in community i can not add any .pbix file. can you tell me how you do that. when i drag and drop site sayd pbix file is not compatible 

thanks

[ryan_mayu]

you are welcome. 

i am not sure why you can't upload the pbix file. maybe you can create another post of this issue. Let's see if community support team can help you on this.

[abc_777]

Hello,

will post a different for that. 

the pbix file you just share me. i think u create second table which is static table and you take only value that has disc_type

the main table that i give is sale table so i just duplicate my sale table and remove other column and keep disc_type and invoice_no.

when when data refresh both sale and second sale table will refreah

so could you please duplicate the main table that i give you then just filter by disc_type?

create two table with following sample and then make relation. coz when data refresh both table will be refreshed. one got all columns another got only invoice_no and Disc_type column

INVOICE_NO	INVOICE_DT	USER_BARCODE	Retail_Price	SQTY	DISC_TYPE	Return	Amount	Store
223050600020	06-05-23	36002	214	1		0	214	100010049
223050600020	06-05-23	36002	0	1	BG0000117	0	0	100010049
CR02230500010	06-05-23	36002	214	0		1	0	100010049
223050600022	06-05-23	36002	214	1		0	214	100010049
223050600022	06-05-23	36002	0	1	BG0000117	0	0	100010049
223051000002	10-05-23	36002	292.29	10		0	2922.9	100010049
CR02230500019	10-05-23	36002	292.29	0		10	0	100010049
223051000004	10-05-23	36002	292.29	10		0	2922.9	100010049
323051000004	10-05-23	36002	292.29	8		0	2338.32	100010049
223051000054	10-05-23	36002	213.75	12		0	2565	100010049
223051000067	10-05-23	36002	292.29	8		0	2338.32	100010049
223051000071	10-05-23	36002	236.25	6		0	1417.5	100010049
223051000086	10-05-23	36002	213.75	6		0	1282.5	100010049
223051000089	10-05-23	36002	214	1		0	214	100010049
223051000089	10-05-23	36002	0	1	BG0000117	0	0	100010049
323051100011	11-05-23	36002	292.29	8		0	2338.32	100010049
223051100068	11-05-23	36002	236.25	3		0	708.75	100010049
223051100084	11-05-23	36002	214	1		0	214	100010049
223051100084	11-05-23	36002	0	1	BG0000117	0	0	100010049
223051500088	15-05-23	36002	273.92	1		0	0	100010049
423051500005	15-05-23	36002	214	3		0	642	100010049
423051500005	15-05-23	36002	0	3	BG0000117	0	0	100010049
223051700023	17-05-23	36002	236.25	12		0	2835	100010049
323051700019	17-05-23	36002	0	1	BG0000117	0	0	100010049

thanks

[ryan_mayu]

the new table is not static table. It's a reference table

if the data in your table changed, it will change as well.

[abc_777]

Hello Ryan,

Really thanks buddy for your continuous help. but bad luck. i have created referance table >> then remove all columns except invoice_no and Disc_type then remove all blank, NULL, 0 vales and it put down to 87,000 or so

when create relation with sale table invoice_no with this sale_referance_table Invoice_no i got Many to many relation not many to one same as your. 

also i remove duplicate from disc_type column

is it must to go to query editor and filter by disc_type = BG0000117 that i seen you did

coz when data refresh and new promotion name came in i.e. BG0000118 or BG0000119 then how to resolve check filter from query edit?

Please guide me

Please help me out

thanks

[ryan_mayu · Accepted solution]

after you move columns and null value, then you need to remove duplicates.

[abc_777]

Hello,

also i remove duplicate from disc_type column

so rather filter by disc_type = BG0000117 how can we solve the issue. promotion name will change when create any promotion. would you mind to have a look into this please

thanks

[ryan_mayu]

not clear about this. could you pls update your sample data and also let me the expected output?

[abc_777]

out put is same that u have given is correct but issue is in your pbix file you filter by BG0000117 

 and i dont want to use filter here as my BG0000117  will be change to BG0000118, BG0000119, BG0000120 etc etc ......

i have duplicate value in disc_type and invoice column and i remove null, remove duplicates, remove blank then create relationshio with sales table invoice_no and new Table Invoice_no many to one its ok but the result is not comes , result shows 

 but i want to see 

like this

thnakx

[ryan_mayu]

updated the sample .pls see the attachment below

[ryan_mayu]

you don't filter in your table, you should filter in the filter visual. 

i remove empty values in the table. maybe you only provide the data with BG0000117 , if you provide more data BG0000118 or BG0000119. These data will also display in the new table.

basicly you can see the disc_type list in the filter visual in your last screenshot. Then you select the disc type to show the corresponding output.

[abc_777]

Hello @ryan_mayu 

Here i have given more sample file to pbix file

please use this. 

https://drive.google.com/drive/folders/1-WQ9JQ36Rb0VqprPY6cGoWHxepdamidg?usp=drive_link

instead of filtering

thx

[ryan_mayu]

don't know why ,i can't open the link, you can just skip the filter step ,just remove duplicates

[abc_777 · Accepted solution]

Hello @ryan_mayu ,

I tried removing the filter but cant resolved,

I have changed the permission access to the folder.

Please try now. just click the link and you be able to download pbix and xlxs file with sameple data. please inform me if have any issues. 

If you still cant get access please share your email address (if possible) so i can email you

https://drive.google.com/drive/folders/1-WQ9JQ36Rb0VqprPY6cGoWHxepdamidg?usp=sharing

thanks