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 Guys,
I created a guide to create a bridge table, but I'm not sure if it's correct. Because this measure ends up giving me all the possibilities between the two dimension tables. What would be the way to do this correctly?
SELECTCOLUMNS(
CROSSJOIN(projetosReaal, _corretores),
"ID_projetos", projetos[idempreendimento],
"ID_corretores", _corretores[idcorretor]
)
Solved! Go to Solution.
Total brokers = COUNTROWS(DISTINCT(UNION(
SELECTCOLUMNS(Leads,"Broker",[idcorretor]),
SELECTCOLUMNS(pastas,"B",[idcorretor]),
SELECTCOLUMNS(ccvs,"B",[idcorretor]),
SELECTCOLUMNS(Repasses,"B",[idcorretor]))))
// distinct count of brokers that are in the tables: Leads, pastas, ccvs and repasses
// The tables are related by ID, but the broker count must be by name
I created a guide to create a bridge table
why would you do that? 🙂 Is there a solid business reason behind that?
What you are doing is re-inventing SUMMARIZECOLUMNS with unrelated tables
Read about TREATAS - it's a way to project filters from one table to another, especially if they are not related.
@lbendlin I would like to do this because my dimension tables are many to many. In this case, for example, I can have one or more projects for each broker and I can have one or more brokers for each project. I need to do some calculations that involve, for example, counting brokers per project, etc. I don't know if there was another way to do this.
It might be better to do that via the common fact table.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
@lbendlinI created a model to try and better show what I would like to achieve. But I have two dimension tables and I need to use both to make some measurements. For example: the sale of each broker per project, the Total of brokers per project. etc...Follow the link below with the file
https://ufile.io/liju6ybx
Your projects table is not a proper dimension table. Try to normalize it.
@lbendlin Okay, I'll do that, but Assuming it was normalized, how would I get the result?
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
@lbendlin I tried to do it in a way that can convey what I need. I also tried to adjust the modeling.
https://ufile.io/plitf8og
Total brokers = COUNTROWS(DISTINCT(UNION(
SELECTCOLUMNS(Leads,"Broker",[idcorretor]),
SELECTCOLUMNS(pastas,"B",[idcorretor]),
SELECTCOLUMNS(ccvs,"B",[idcorretor]),
SELECTCOLUMNS(Repasses,"B",[idcorretor]))))
// distinct count of brokers that are in the tables: Leads, pastas, ccvs and repasses
// The tables are related by ID, but the broker count must be by name
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 | |
97 | |
75 | |
63 | |
62 |
User | Count |
---|---|
139 | |
104 | |
104 | |
80 | |
66 |