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.
Hi,
I am searching since a week but cannot find a working solution. Even if it seems a simple problem.
I have two tables which have a 1 to n relationship:
The first has data about Location. Each location has a ID.
The other table contains reports of visits from those locations with date and customer information. Thus a location can be visited more often.
I wanted to visualize "visited locations" VS "not visited locations" in a bar chart. I thought it would be simple but it did not work.
So I used a lookup-function to check if the location has been visited:
Solved! Go to Solution.
Hi @EdList ,
You want to show counts in bar chart, right?
If so, you need to create another table with "Yes" and "No".
Then, create the count measure like so:
Measure 2 =
VAR YN =
SELECTEDVALUE ( 'Table'[Column1] )
RETURN
CALCULATE ( COUNT ( Shops[ID] ), FILTER ( Shops, [Visited] = YN ) )
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@EdList - It's difficult to understand what is going on from your description. Please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
@Greg_DecklerSorry but my post yesterday has been deleted somehow....
I try to explain in more detail and easier:
I have two tables:
Table 1 - Shops
ID | Shop name | Shop Location | Visited with vlookup |
1234 | Bradstore | Vienna | yes |
4332 | Ninastore | Berlin | yes |
3212 | Samstore | Spain | no |
Table 2 - Visits
Salesperson | Visit-ID | Date | ID Store visited |
Tom | 3223 | 13-01-2020 | 1234 |
Jack | 3213 | 14-02-2020 | 1234 |
Sam | 3212 | 01-04-2020 | 4332 |
Eva | 2123 | 01-05-2020 | 1234 |
So the tables have a 1 to n connection.
I wanted to show in a chart how many stores are visited and how many are not. As a location can be visited more often a normal vlookup would fail. So i just make a new column to check if the store has been visited.
Lookup:
This works fine so far.
My problem is that i want to make a date-slicer to check if a certain location has been visited in a certain month.
Any clue how to get this to work??
Thanks.
Jakob
You are better off getting your result through a measure over a calculated column. Here are the steps:
1. Make a relationship between your Shops and Visits tables on the StoreID column (1:many from Shops to Visits)
2. Add a table visual with Store ID, Shop Name, and Shop Location and the measure in #3. Make sure you use the columns from the Shops table.
3. Make a measure like this and add it to the table visual
Visited = IF(ISBLANK(Countrows(Visits), "No", "Yes")
4. Add a Date table to your model, so you can slice by Date info. Make a 1:Many relationship from it to the Date column of your Visits table. See this article for more details - https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @mahoneypat
thank you very much for your help.
Your solution is exactely what I had already. However I am happy that you got my problem right 🙂
I check if a store is visited by the this calculated column:
Except for the date table. I added it but the calculated visits visuals still does not change when i filter with the slicer.
Thanks
Jakob
Your last response states that you put your expression in a calculated column. My response provided a measure expression. Measures respond to slicer selection, while calculated columns do not. Please try using the measure expression I provided, along with the other suggestions for tables and relationships.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypatThanks. I use a measure now and when using table or a matrix visual it works perfectly fine also when slicing by date!!
I use Shop-ID as value and the measure is used in the quickinfo.
However it fails using a chart visual because it does not show any result. Any idea for this? Than everything would be solved for me 🙂
Thank you in advance.
Jakob
Hi @EdList ,
You want to show counts in bar chart, right?
If so, you need to create another table with "Yes" and "No".
Then, create the count measure like so:
Measure 2 =
VAR YN =
SELECTEDVALUE ( 'Table'[Column1] )
RETURN
CALCULATE ( COUNT ( Shops[ID] ), FILTER ( Shops, [Visited] = YN ) )
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Icey
thanks again for your soluation. Works fine.
However I am struggeling now showing the visits in a bar chart where the visits are splitted by group. My data looks like this now:
I have two tables "Visits" and "Shoplist" as shown on the left side. I applyied your solution and got the bottom right chart which is correct. I also can filter by date which works perfectly fine as well.
What I want now is to visualize the visits splitted by the Shopgroup (chart in the upper right corner).
I tryied several other measures but none work.
Any suggestions?
Thanks.
Jakob
Hi @EdList ,
Please check if this is what you want:
Put "Shopgroup" column into "Axis" field.
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Icey ,
thanks again. Yes this is what I want. However this brings up another problem: I cannot sort by number of shops (number of ID's).
You got a solution for that?
My pbix is available here: https://filebin.net/qd8v5yp216r9cy2u
.
As you can see on the picture the expected result should be EAS>MES>SEP>IAS because I sort by the quickinfo 'number of ID (shops)'. I guess it does not work due to the measure?
Thanks again.
BG Jakob
Hi @EdList ,
Create another measure like so:
Measure = CALCULATE ( COUNT ( Shoplist[ID] ), ALL ( 'YN-Table'[YN] ) )
And then use this measure to sort.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Great thanks!!
@Greg_Deckler Thank you for your reply. Yes I checked the links. I think I am just bad in explaining.
Maybe I just tell you what I want to visualize:
As i mentioned I have two tables:
table 1
ID | Where were you? | Customer | Info | Date |
22342 | Vienna | Frank | sold him something | 2020-01-23 |
23421 | Berlin | Tom | just drank coffee | 2020-02-01 |
22342 | Vienna | Frank | sold him something | 2020-03-01 |
22342 | Vienna | Frank | sold him something |
table 2
ID | Town | visited* |
22342 | Vienna | yes |
23421 | Berlin | yes |
23431 | Spain | no |
This is an 1 to n connection.
What I want is to show in a chart which IDs from table 2 have been visited. As I had no clue how to visualize that I just added a lookup-function in table 2:
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 |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |