Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
My problem :
Table 1 :
Key | Name |
A | Mike |
B | Alexander |
C | Rebecca |
Table 2 :
Key | Value | Date |
A | 10 | May 2024 |
A | 20 | Jul 2024 |
B | 30 | Jan 2024 |
The relationship between table 1 and 2 is on the key value
In Power BI, i create this array
Key | Sum Value |
A | 30 |
B | 30 |
But i want to have this :
Key | Sum Value |
A | 30 |
B | 30 |
C | 0 |
C doesn t show cause is not on the table 2. But how to have the C on the array without merge in power query the 2 tables ?
Solved! Go to Solution.
Hi @krimionn23
Follow the following steps and compare your results as shown in the screenshots.
1- Create a dummy measure
DummyMeasure = 0
2- Create a second measure to sum the Value in the second table, adding the DummyMeasure as well.
M2 =
CALCULATE(
SUM('Table'[VALUE]) +[Measure],
CROSSFILTER('Table'[KEY], TBL1_1[KEY], Both)
)
you will get the desired output as shown below.
Did I answer your question? Mark my Post as Solution. Thanks
hi @krimionn23
You can achieve the desired results in multiple ways.
1- Merging the existing tables into a new table
2- Left outer join to append the empty rows
3- change the context of your filter and add the keys from table 1 to the visual.
This 3 solutions or only 1 solution with 3 steps ? I need a solution without a merge between 2 tables , Only with the data model
Hi @krimionn23
Follow the following steps and compare your results as shown in the screenshots.
1- Create a dummy measure
DummyMeasure = 0
2- Create a second measure to sum the Value in the second table, adding the DummyMeasure as well.
M2 =
CALCULATE(
SUM('Table'[VALUE]) +[Measure],
CROSSFILTER('Table'[KEY], TBL1_1[KEY], Both)
)
you will get the desired output as shown below.
Did I answer your question? Mark my Post as Solution. Thanks
User | Count |
---|---|
100 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |