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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Skilled Sharer
Skilled Sharer

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Top Solution Authors
Top Kudoed Authors