topic Re: DAX measure for returning specific values corresponding to slicer in DAX Commands and Tips

DAX measure for returning specific values corresponding to slicer

Magnus-CPH-DK — Wed, 20 Jul 2022 10:43:18 GMT

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

Re: DAX measure for returning specific values corresponding to slicer

daXtreme — Wed, 20 Jul 2022 12:35:42 GMT

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

Re: DAX measure for returning specific values corresponding to slicer

Magnus-CPH-DK — Wed, 20 Jul 2022 14:05:45 GMT

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.

"Supplier Name" is the same measure you and I worte - Works just fine
'VirkMain'[cvrNummer] is the Supplier ID columns from the Supplier table - Works in the table, however, it cannot find the accurate value in the matrix (it returns different values depending on if I return Sum, Min, Max and so on).
"Sum of Supplier ID" is the measure I cannot get to work. If i mark it with "Don't summarize" the whole table shows blank values.
"Supplier Branchetekst" is a test measure made in the same way as the other 2, which holds a text value from the supplier table - works just fine.
"Supplier Enhedsnummmer" is a test measure made in the same way as the other 2, which holds a numeric value from the supplier table - works just fine.
'Virkmain'[branchetekst] is a column from the Supplier table that holds an additional text value of a supplier - works just fine
And finally, 'Virkmain'[enhedsNummer] is a columns from the Supplier table that holds an additional numeric value of a supplier - works just fine. Returns the same value as the "Supplier Enhedsnummer" measure.

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.

Re: DAX measure for returning specific values corresponding to slicer

daXtreme — Wed, 20 Jul 2022 14:12:04 GMT

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.

Re: DAX measure for returning specific values corresponding to slicer

Magnus-CPH-DK — Fri, 22 Jul 2022 08:19:40 GMT

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