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 all!
I am still pretty new to Power BI.
I am building a report that first of all is used for searching values and returning corresponding values in a matrix/table to create an overview over supplier contracts, suppliers and the suppliers' ID's.
The two tables I am using is a Contract Fact table "Fact_Kontrolregister_A_&_D" containing a contract name (aftalenavn) and a supplier ID (cvrNummer) as well as a Supplier Dim table "VirkMain" containing all unique supplier ID's (cvrNummer) as well as the supplier name (navn). There is a single-directional one-to-many relationship from VirkMain to Fact_Kontrolregister_A_&_D with supplier ID (cvr) as the connecting key:
In the example below I have a supplier contract slicer and a table that ought to show the supplier and supplier ID corresponding to the selected supplier contract. I am able to return the supplier name (left side) but I fail to return the supplier ID (right side)
The two measures I use are much alike, but only "Supplier Name" is working where "Supplier ID" does not return a value:
Supplier Name =
CALCULATE(
MAX(VirkMain[navn]),
FILTER(
'Fact_Kontrolregister_A_&_D',
'Fact_Kontrolregister_A_&_D'[aftalenavn] = _Measures[Selected Aftalenavn]),
FILTER(
'Fact_Kontrolregister_A_&_D',
'Fact_Kontrolregister_A_&_D'[cvr_HL/UL?] = "Hovedleverandør")
)
Supplier ID =
CALCULATE(
MAX(VirkMain[cvrNummer]),
FILTER(
'Fact_Kontrolregister_A_&_D',
'Fact_Kontrolregister_A_&_D'[aftalenavn] = _Measures[Selected Aftalenavn]),
FILTER(
'Fact_Kontrolregister_A_&_D',
'Fact_Kontrolregister_A_&_D'[cvr_HL/UL?] = "Hovedleverandør")
)
Any ideas how to make the second measure work?
Thanks a lot!
- Magnus
Solved! Go to Solution.
Supplier ID =
var SupplierId =
calculate(
// Just take the supplier id from the fact table
// instead of from the dimension...
SELECTEDVALUE( 'Fact_Kontrolregister_A_&_D'[cvrNumber] ),
keepfilters(
'Fact_Kontrolregister_A_&_D'[cvr_HL/UL?] = "Hovedleverandør"
)
)
return
SupplierId
The id's must be same in both the dim table and the fact if they are joined on this field. But I don't understand why one of the formulas does return the correct answer but the other doesn't. This is pretty weird. There must be something going on with your data, I presume. But can't be sure since have no access to it... Try the above.
Thanks again, @daXtreme
This is a bit embarassing... the small sigma sign next to the measure was there because I somehow created a column instead of a measure. I didn't think that was even possible, when it is not connected to a table. Probably the reason I didn't take notice.
Anyhow, both my code and yours work, so thanks again for helping me out!
Have a nice day,
Magnus
Supplier Name =
var SupplierNames =
CALCULATETABLE(
summarize(
'Fact_Kontrolregister_A_&_D',
VirkMain[navn]
),
keepfilters(
'Fact_Kontrolregister_A_&_D'[cvr_HL/UL?]
= "Hovedleverandør"
)
)
var Result =
if( countrows( SupplierNames ) = 1,
SupplierNames
)
return
Result
Supplier ID =
var SupplierIds =
CALCULATETABLE(
summarize(
'Fact_Kontrolregister_A_&_D',
VirkMain[cvrNummer]
),
keepfilters(
'Fact_Kontrolregister_A_&_D'[cvr_HL/UL?] = "Hovedleverandør"
)
)
var Result =
if( countrows( SupplierIds ) = 1,
SupplierIds
)
return
Result
Thanks a lot, @daXtreme .
The first measure Supplier Name that you wrote works properly and returns the same value as the Supplier Name measure that I wrote.
However, the Supplier ID measure that you wrote seems to return blank values just as my Supplier ID measure.
I have tried troubleshooting a bit and created a couple of measures more and inserted them in the table alongside a couple of columns which holds the same values as the measures I am trying to get to work.
Furthermore, I have made a matrix which among others shows the Supplier Name and Supplier ID at the top of the matrix.
Conclusion: The table is able to return the values I am looking for, whether text or nomeric and whether they are made as measures or coumns from the Supplier table. The only outlier is the Supplier ID (cvrNummer) which is the connecting key between the two tables. The Supplier ID measure is the only measure which has a small Sum sign (the sigma sign) next to it.
Since the matrix visual does not show the correct Supplier ID if I include it as a columns value, I still need to fix the Supplier ID measure.
Supplier ID =
var SupplierId =
calculate(
// Just take the supplier id from the fact table
// instead of from the dimension...
SELECTEDVALUE( 'Fact_Kontrolregister_A_&_D'[cvrNumber] ),
keepfilters(
'Fact_Kontrolregister_A_&_D'[cvr_HL/UL?] = "Hovedleverandør"
)
)
return
SupplierId
The id's must be same in both the dim table and the fact if they are joined on this field. But I don't understand why one of the formulas does return the correct answer but the other doesn't. This is pretty weird. There must be something going on with your data, I presume. But can't be sure since have no access to it... Try the above.
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |