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 have a table (Table 1) with organisations and another table (Table 2) with users per organisation and whether they are claimin or not.
I want to add a calculated column in Table 1 (in blue below) to show the number of users claiming for each organisation (coming from Table 2). The common field in both tables is the organisation id.
How can I do this please? Thanks
Table 1 | |
Organisation | Users Claiming |
A | 1 |
B | 2 |
Table 2 | ||
User | Organisation | UserClaiming Y/N |
x | A | y |
y | A | n |
z | B | y |
h | B | y |
Solved! Go to Solution.
Hi @mq2020 ,
Please try to create the following measure to get the flag of every organization with claim:
Org Claiming YN =
VAR sumofClaimU =
CALCULATE ( DISTINCTCOUNT ( 'User'[User] ), 'User'[User Claimed Y/N] = "y" )
RETURN
IF ( sumofClaimU > 0, "Y", "N" )
Best Regards
Rena
@mq2020 assuming you have a relationship between these two tables on Organisation column, just add a measure and in table visual , drop organization and this new measure.
Measure =
CALCULATE( COUNTROWS ( Table2 ), Table2[User Claiming] = "Y" )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi!
I do have a relationship of 1 to Many.
What I am trying to do is display a line chart with av.sales for 2 groups (Organisations with users claiming vs Organisations with users not claiming).
The sales are in a 3rd table with has a relationship of Many to 1 with Table 1 (Organisations) so I end up with:
Sales (Many to 1) -> Organisations (1 to Many) -> Users
When i put together info from the sales table and the Users table it is not working, hence, I figured, it may be easier if i add the column I need in the Organisations table (i.e if that organisation has any user claiming)
Thanks,
Hi @mq2020 ,
Could you please provide some sample data in table "Sales"(exclude sensitive data)? What's your expected result? What you want is to get the average of sales? If yes, then whether it need to calculate based on certain conditions?
What I am trying to do is display a line chart with av.sales for 2 groups (Organisations with users claiming vs Organisations with users not claiming).
Best Regards
Rena
These are the tables and relationships:
Sample data of the 3 tables:
Sales ->
Organisations ->
Users ->
At the moment I am displaying Av/units per organisation (a measure in the model) over time (visual below).
What I'd like to do is split this into 2 groups (organisations with users claiming vs organisations with users not claiming).
When I try adding this field (User Claimed Y/N) to the model (in the legend part of the visual), it's not doing what I expect.
What I expect is the following (which I've done in excel to demonstrate)
@mq2020 It is a bit of confusing model, what happens if users under an organization have both yes and no, which bucket it will fall or this will never happen? Yes/No it is at a user level and you are viewing the data at org level, so you need to clarify your business rules.
There are many ways to do it but it will depend on your answer above.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi again,
So I think I found a way to add that extra column however when I try to use IF to do the classification it's giving me a "circular dependancy error" - any idea how to get around this?
I first created a column in table Users to change Y N to 0 and 1 (Just so i can then sum this in the next step)
Hi @mq2020 ,
Please try to create the following measure to get the flag of every organization with claim:
Org Claiming YN =
VAR sumofClaimU =
CALCULATE ( DISTINCTCOUNT ( 'User'[User] ), 'User'[User Claimed Y/N] = "y" )
RETURN
IF ( sumofClaimU > 0, "Y", "N" )
Best Regards
Rena
Thank you so much @v-yiruan-msft !
I actually applied the same to a calculated column istead of measure so that I could use the column to split my line chart using the Legend Box and use the grouping in slicers which the measure wasnt allowing me to do.
Hi @parry2k
If 0 users claim-> Organisation Claiming No
If 1 or more than one users claim - Organisation Claiming Yes
Initially I just wanted to fix it my adding a column to the table Organisations to count the number of users claiming for each organisation, then I can add an extra column to do the classification with IF.
It is the count of users claiming that i dont know how to add in the Organisations table - if I could get help with this then that should solve the issue I believe. Thank you so much
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 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |