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 two tables that I am importing into a dashboard. The first is a table created by Microsoft Forms answers. The second table is a cross reference table that shows which targets were asked about on which Form #. I am trying to build a dashboard that will show who has or has not approached which targets but also show when its unknown.
So for John, he approached no targets on Form #1 (Best Buy, JC Penny, Sears, Walmart), only approached Ebay on Form #2, and has not answered Form #'s 3 or 4. I have relationships that look like this
So I can see John aproached Ebay and didn't approach the customers on Form 1 or 2. But its also showing he didnt approach customers on Form 3 or 4 which he has not submitted answers for. Any idea how to make those customers show a null or unknown text since he hasnt filled out that form yet?
Solved! Go to Solution.
What volume of data are you working with here? You could try to merge the tables in the Power Query Editor, which will increase the total volume of data but acheive the results you're looking for:
The table above uses 'DIM - Entity'[Target], 'Form Answers'[Name] and 'Form Answers'[Approached Target?], and then applies the 'Show items with no data' to the 'DIM - Entity'[Target] field.
In your current dataset the 'Yes' show up fine, the 'unknown' show as blank, but the 'No' also show as blank. One way to fix this is Merge Queries;
In the Transform data Query Editor for the Form Answers table, click on the Home tab and select Merge Queries. Select the Form Answers and Cross Reference tables and choose the Form # column from each table. Choose Left Outer as the Join kind. Then click the double arrows on the new Cross Reference column and select 'Target' to expand the column. I renamed this 'Available Targets'.
Close and load the changes and your table should look like the photo above. You can then use DAX to replace the blanks with the word 'Unknown' if that's necessary.
If this solves your problem, please mark as solution. If you have questions about this solution, just let us know.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
What volume of data are you working with here? You could try to merge the tables in the Power Query Editor, which will increase the total volume of data but acheive the results you're looking for:
The table above uses 'DIM - Entity'[Target], 'Form Answers'[Name] and 'Form Answers'[Approached Target?], and then applies the 'Show items with no data' to the 'DIM - Entity'[Target] field.
In your current dataset the 'Yes' show up fine, the 'unknown' show as blank, but the 'No' also show as blank. One way to fix this is Merge Queries;
In the Transform data Query Editor for the Form Answers table, click on the Home tab and select Merge Queries. Select the Form Answers and Cross Reference tables and choose the Form # column from each table. Choose Left Outer as the Join kind. Then click the double arrows on the new Cross Reference column and select 'Target' to expand the column. I renamed this 'Available Targets'.
Close and load the changes and your table should look like the photo above. You can then use DAX to replace the blanks with the word 'Unknown' if that's necessary.
If this solves your problem, please mark as solution. If you have questions about this solution, just let us know.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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 |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |