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.
I got stuck on how to sort by measure based on another measure. On my case I have 2 measures called: Savings and Spend.
In order to support bar chart:
Whenever end-users choose 'Sort by Saving' I would like to sort by Spend behind the scene. I know Power BI support function called 'Sort By Column' on modeling but it's disable if I choose measure `Saving`
Is there anyway to work around for this case?
Thanks a lot
Hi @cuongle,
I think this function only works on columns.(if you try to expand the dropdown panel, you can only find the columns)
Regards,
Xiaoxin Sheng
Thanks @v-shex-msft, do you know is there any way to work around with this? I cannot use column as measure `Spend` and `Savings` is dynamically calculated based on the currency that user chooses.
Hi @cuongle,
Can you share some detail contents about this formulas and table structures? If these measures not the dynamic measure which changes by slicer, you can modify the formula to transform it to calculate column.
Regards,
Xiaoxin Sheng
Assume I have table called InvoiceDetail, which contain column:
SpendInEUR
SpendInGBP
SpendInUSD
SavingsInEUR
SavingsInGBP
SavingsInUSD
So on the Power BI, we allow user chooses which currency they want to show using ChicletSlicer like that:
And then we calculate measure Spend and Savings based on which currency is opted
Spend = IF ( LASTNONBLANK ( 'DefaultCurrency'[Code], 1 ) = "GBP", SUM ( InvoiceDetail[SpendInGBP]), IF ( LASTNONBLANK ( DefaultCurrency[Code], 1 ) = "USD", SUM ( InvoiceDetail[SpendInUSD] ), SUM ( InvoiceDetail[SpendInEUR] ) ) )
Savings = IF ( LASTNONBLANK ( 'DefaultCurrency'[Code], 1 ) = "GBP", SUM ( InvoiceDetail[SavingsInGBP]), IF ( LASTNONBLANK ( DefaultCurrency[Code], 1 ) = "USD", SUM ( InvoiceDetail[SavingsInUSD] ), SUM ( InvoiceDetail[SavingsInEUR] ) ) )
Thanks for your help
@cuongle Did you ever find a trick to work around this? I am dealing with the exact same situation.
Hi @cuongle,
Power bi not support dynamic calculated column/table based on slicer, so it is impossible to convert your formula to calculate column.
Reference link:
Calculated Column/Table Change Dynamically According to Slicer Selection in the Report.
Regards,
Xiaoxin Sheng
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |