Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have one excel sheet that is my sales information. I have another excel sheet that lists my discontinued items.
I am trying to add either a new column or measue to my sales information query based on the discontinued items list.
Here's some fake date. I am trying to put a Y or a 1 or some other designation in the discontinued item column of Excel 1 in the rows where the item number of Excel 1 is in the list of Excel 2 so I can use a slicer to filter my visualizations. I currently have a one to many relationship between the two item number columns.
Solved! Go to Solution.
HI, @Anonymous
After my research, you can do these as below:
Step1:
you need to expand List in Edit Queries if you haven't unfolded it yet.
Reference : https://powerbi.microsoft.com/en-us/blog/how-to-expand-a-list-of-records-in-a-query/
Step2:
there are two simple ways to achieve your requirement
1.since you have created the relationship with them, then drag the field productID from table2 into page level filter or
report level filter then set filter show blank
Result:
2. Use LOOKUPVALUE Function to add a state column
State = IF(ISBLANK(LOOKUPVALUE(Table2[ProductID],Table2[ProductID],Table1[Product ID])),"Y","N")
then drag the field State into page level filter or report level filter then set filter show Y,
or drag the field State into a slicer and filter Y
Result:
here is pbix, and these two ways are shown in two pages, please try it.
https://www.dropbox.com/s/8jdxom5tcu91v1f/Merging%20Queries%20using%20SharePoint%20lists.pbix?dl=0
Best Regards,
Lin
HI, @Anonymous
After my research, you can do these as below:
Step1:
you need to expand List in Edit Queries if you haven't unfolded it yet.
Reference : https://powerbi.microsoft.com/en-us/blog/how-to-expand-a-list-of-records-in-a-query/
Step2:
there are two simple ways to achieve your requirement
1.since you have created the relationship with them, then drag the field productID from table2 into page level filter or
report level filter then set filter show blank
Result:
2. Use LOOKUPVALUE Function to add a state column
State = IF(ISBLANK(LOOKUPVALUE(Table2[ProductID],Table2[ProductID],Table1[Product ID])),"Y","N")
then drag the field State into page level filter or report level filter then set filter show Y,
or drag the field State into a slicer and filter Y
Result:
here is pbix, and these two ways are shown in two pages, please try it.
https://www.dropbox.com/s/8jdxom5tcu91v1f/Merging%20Queries%20using%20SharePoint%20lists.pbix?dl=0
Best Regards,
Lin
Thank you! The second solution (LOOKUPVALUE) worked best for me.
Eric
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
99 | |
80 | |
77 | |
66 |
User | Count |
---|---|
134 | |
106 | |
104 | |
85 | |
73 |