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.
OK, so I have two tables of financial data:
Table 1
Code Date Revenue1 Revenue 2
Tabel 2
Code Date Revenue_Planned1 Revenue_Planned2
I want to slice both tables with one date clicer and one code slicerthat shows me the sum of (Revenue1 and Revenue 2) AND the sum of (Revenue_Planned1 and Revenue_Planned2) for the selected month(s), ideally in the KPI visual. (TO see how we are tracking revenue vs planned revenue)
But I can't add two active relationships.
There is a link between the date fields, and the Code fields, but only one can be active.
I added a seperate "Code" list for the codes (one line per) and did 1-many between that and the tables, nope, still couldn't work, as their is an indirect relationship (in other words, this doesn't help at all)
I tried the UseRelationbship function, but it's a mystery to me how that thing is supposed to work.
Solved! Go to Solution.
Hi @Anonymous ,
We can create a medium table to be the slicer.
1. create calculated table.
MonthTable = DISTINCT(UNION(DISTINCT(Table1[Date]),DISTINCT(Table2[Date])))
CodeTable = DISTINCT(UNION(DISTINCT(Table1[Code]),DISTINCT(Table2[Code])))
2. create relation ship between four table
3. use the column in medium table as the silcer.
4. create measure using the source table.
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
you should create a date table and use its columns for slicing.
Here is a explanation how to create it:
https://kohera.be/blog/power-bi/how-to-create-a-date-table-in-power-bi-in-2-simple-steps/
https://www.youtube.com/watch?v=gyvhM5eiT0U
If this post was helpful may I ask you to mark it as solution and give it some kudos?
Have a nice day!
BR,
Josef
I'm sorry, this does't work at all.
This just moves the conflict from the Code field to the Date field, with the same issue.
"You can't create a direect active relationship between Table 1 and Table 2, becuase that wold create ambiguity between Date and Table 1"
Hi,
may I ask you to provide some sample data? So that we don't need to guess.
BR,
Josef
Table 1
Code Date Revenue1 Revenue 2
Client A Jan $1000 -$500
Client B Jan $2000 $500
Client C Jan $1500 $50
Client D Jan $800
Client A Feb $1500 $800
Client B Feb $1000 $50
Client C Feb $100 $500
Client D Feb $800 $200
Tabel 2
Code Date Revenue_Planned1 Revenue_Planned2
Client A Jan $2000 $500
Client B Jan $1000 $1500
Client C Jan $1500 $500
Client D Jan $800 $200
Client A Feb $1500 $100
Client B Feb $1000 $50
Client C Feb $100 $500
Client D Feb $800 $200
So I want to see
Date Slicer: JAN
Code Slicer: Client C
Planned Revenue: $2000
Actual Revenue $1550
And to be able to change those 2 slicers to provide the data for one of more clinets and one or more dates.
Note that merging all possible dates and codes is not a tenable solution.
Hi @Anonymous ,
We can create a medium table to be the slicer.
1. create calculated table.
MonthTable = DISTINCT(UNION(DISTINCT(Table1[Date]),DISTINCT(Table2[Date])))
CodeTable = DISTINCT(UNION(DISTINCT(Table1[Code]),DISTINCT(Table2[Code])))
2. create relation ship between four table
3. use the column in medium table as the silcer.
4. create measure using the source table.
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Wow, fantastic, thanks!
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |