AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
Power bi matrix show in tabular form11/6/2023 ![]() Update the properties “Format String Expression” of each Calculation Item, I think you can go further to make this in dax or make a reference table to make it dynamic as well. Don’t worry, the calculation group will overwrite them. In Power BI, create related measure names used in the dax, and put a SELECTEDMEASURE() in it. Within that the switch formula handles the legend/category in line chart, and using ISSELECTEDMEASURE() to do a conditional switch calculation based on the measure I used for different visuals. You will realize that there is “Name” column automated generated, you will use it as the slicer to switch between Calculation Items.īecause I know that I can use this to overwrite the existing measures in Power BI dataset, I made below dax to do my job. So what I have done is like below steps: 1. There is one thing you will be aware of, once you add “measure name” as slicers in your report page, all the measures in that page will be treated as part of the calculation within the group, which means it will overwrite your existing measures, no matter what it is. There might be a way to use one calculation group on top of the other one to make this work, but I didn’t get the luck. My case is sort of different, by using calculation group I will need to use the “measure name” as one slicer to change different metrics, but I will also need to apply another layer of the switch to bring “current year”, “last year” and “target”. If you’d like to learn more about this, I highly recommended to read SQLBI posts And if you have a new measure in like Booking Revenue, the calculation group will do the same job for you. The selectedmeasure() function will replace your measures like Bookings when you use it in a report page. By using calculation group, you don’t need to do that, just simply created. Of course, you can use a switch to do the similar calculation, but you will need to define all those measures in a switch measure first. Calculation Group is a very powerful function helping save tons of time to create redundancy measures, for example, you have measure like Bookings, Conversion Rate, and you also need apply time intelligence on top of them like YTD, MTD. Now what we need to do is creating a calculation group. And when you open it, Tabular Editor will automatically link to your Power BI dataset (Tabular Model) Let me introduce Tabular Editor first, you can downloaded from and when you restart your Power BI, you will see it under External Tools tab. Then I found a great solution in Tabular Editor, linked as External Tools of Power BI, the calculation groups which can alter the format of a switch type of measure into different format but stayed as value not string. Because once you applied Format() function, the value will be converted to a string, and line visuals won’t allow us to add a string as values. But there is always issue about how to use it in non-Matrix visual, like line chart. ![]() If it’s a Matrix visual, on top of that Switch measure we can add another Switch to format each values for currency or percentage. Switch(True(),) or Switch(selectedvalue(),) and insert different measures in it. Hopefully I will have more excited project related to Power BI in the future to give more content, or maybe I will change to blog some tips and tricks instead of solutions.īack to the topic, every Power BI user understands “Switch” is a quite powerful DAX formula to make dynamic calculations. Recently I noticed that some cool update on Power BI side, and there are some good solutions I am willing to share, so I decide to keep blogging it from now on. ![]() I have been working on some interesting projects last a few months, but majorly about data migration/transformation with google analytics, python, etc. Reviewing my last post on the end of the 2019, I am feeling bad that I have not updated any new blogs since then, even I can find the reason is so many things happened last year, especially the COVID hit.
0 Comments
Read More
Leave a Reply. |