Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Khushboo19
Regular Visitor

Need help in my DAX measure query

I have a power BI template (shown in image below)

Khushboo19_1-1692960857283.png

In my power BI report, I am trying to update the values for each row in column B. I have the DAX calculations for rows which are not headings like row 3 to 8 and so on, but due to this, my other rows(like row 1 and 2) are getting removed from the power BI report: 

Khushboo19_2-1692961136872.pngKhushboo19_3-1692961191086.png

In the above DAX query, I tried using CurrentItem as the last switch stmt but it is throwing the error: switch does not support values of type true/false with values of type number. consider using value or format func to convert one of the values

 

@daxcommunity @measures 

1 ACCEPTED SOLUTION

Thanks for such a hard work Sakiko, really appreciate it. 
But, today I got to know where the issue was in the DAX. in my dax query if I format "BS Values " to text field then it is showing up result for all the rows (even for the headings which don't have any calculated value in the o/p). As you can see assets and current assets are visibile now

Khushboo19_0-1693306857566.pngKhushboo19_1-1693306891144.png

 

View solution in original post

6 REPLIES 6
DataNinja777
Super User
Super User

Hi Khushboo19,

 

Instead of trying to address financial statements aggrgation issue with creating a complex dax measure, I address this isssue by creating proper mapping table (dimension table) which should be linked in the data model with fact tables (like trial balance and transations tables).  Sorting out GL account mapping to each financial statements aggregation disclosure lines is the first thing I would do in automaing financial reporting. Once this is done, it would be just a matter of aggregating the trial balance numbers which should add up to zero to be mapped to each financial statements disclosure line.  

Thanks for looking into it Sakiko. 

 

I think, there is some gap, below is how my data looks like: 

Khushboo19_0-1693210582661.png

The challenge is not incaluclating the value for the category or sub category. The challenge that I am facing is in the switch query which is hiding up the headings in my template table. If you see below image, the headings "Assets" and "Current Assets" are visible, but when I import the data using prev shown switch query they are gone as I don't have any logic written to populate that:

Khushboo19_1-1693211038110.png

Hope, I was able to explain my question.

Hi Khushboo19

 

Did you get that fact table from an excel file?  Normally, I download such information from ERP (like SAP's trial balance) at the GL account granularity level to prepare the financial statements, but your fact table is already aggregated at the subcategory granularity level.  That's OK, I guess, but I think it is better that you do not repeat in your fact table the aggregation mapping like category and Balance Sheet Type.  Instead, I would separate the fact table (the BS values and Year and Subcategory fields) and dimension table (Subcategory, Category and Balance Sheet Type fields), and create a relationship using the subcategory field (if this is the smallest granurality key in your data model).  After you created the data model, I guess there are multiple ways to accomplish your task of subtotalling of the BS line items with proper balance sheet sort order (i.e., most liquid asset types at the top to least liquid liability types, then share capital and retained earnings at the bottom. If you'd like, please share with me the sample file, and I can try to sort out the data model.

The file is from one of the Udemy course that I am doing and it is teaching on the ease of financial reporting. Along with data model, if you could givea shot at the DAX on your side to see if it is working for you https://docs.google.com/spreadsheets/d/1X5HMbg-IeQYfMhCXCGggkepEdMCj6Lby/edit?usp=drive_link&ouid=10... 

Hi @Khushboo19 

Thanks for sharing the excercise. I had a look at the material, and noted that the balance sheet does not balance because Total assets ≠ Total liabilities and equity in the raw data table:

Sakiko_2-1693223860860.png

Also, Power BI also showed the same output.  

Sakiko_3-1693223909819.png

In order to make the data model simple, I've cleansed the messy BS Template table and produced the mapping dimension table which will be linked with the fact table with the BS numbers.   The part which was tricky for this excercise was the fact that there were 3 "Other" items (one in assets, one in liablities, and one in equity), and "Deferred income tax" (one in assets and one in liabilities), so I had to make them unique by concatenating with other columns to distinguish from one another.   After these clensing activities are done, I could create a relationship between BS Template dimension table and Balance Sheet Data fact table. Before creating the relationship, I unpivoted Balance Sheet Data fact table so that I only needed to create one aggregation measure for the balance sheet and not 5 measures for each of the years between 2014 and 2018.  

Sakiko_4-1693224133427.png

Sakiko_5-1693224534669.png

 

Hi @Khushboo19 

Thanks for sharing the excercise. I had a look at the material, and noted that the balance sheet does not balance because Total assets ≠ Total liabilities and equity in the raw data table:

Sakiko_2-1693223860860.png

Also, Power BI also showed the same output.  

Sakiko_3-1693223909819.png

In order to make the data model simple, I've cleansed the messy BS Template table and produced the mapping dimension table which will be linked with the fact table with the BS numbers.   The part which was tricky for this excercise was the fact that there were 3 "Other" items (one in assets, one in liablities, and one in equity), and "Deferred income tax" (one in assets and one in liabilities), so I had to make them unique by concatenating with other columns to distinguish from one another.   After these clensing activities are done, I could create a relationship between BS Template dimension table and Balance Sheet Data fact table. 

Sakiko_4-1693224133427.png

Before creating the relationship, I unpivoted Balance Sheet Data fact table so that I only needed to create one aggregation measure for the balance sheet and not 5 measures for each of the years between 2014 and 2018.  

Sakiko_5-1693224534669.png

 

The data model for this task is shown below:

Sakiko_6-1693224652821.png

 

Although all the numbers in the fact tables were shown as positive sign, the assets have to be aggregated using positive sign, while the liabilities and equity have to be aggregated using negative sign, so I've written the dax measure below:

Sakiko_7-1693224924671.png

 

The order of liquidity on the BS (the most liquid assets at the top to the least liquid liabilities at the bottom) was sorted out in the data view using the sort by column icon as shown below.  

Sakiko_8-1693225295488.png

If you'd like to have the pbix file for this excercise which I've done, please let me know.  

 

Thanks for such a hard work Sakiko, really appreciate it. 
But, today I got to know where the issue was in the DAX. in my dax query if I format "BS Values " to text field then it is showing up result for all the rows (even for the headings which don't have any calculated value in the o/p). As you can see assets and current assets are visibile now

Khushboo19_0-1693306857566.pngKhushboo19_1-1693306891144.png

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.