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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
johnce1234
New Member

Switch Into another Measure

Good Evening, 

 

I am having a little issue with a measure that goes into my switch

 

I have a schema as follows

 

G/L Entries (Base data)  (Mapped to Account Category code 1 to many)

G/L Account Categories (Mapped to report schema via the codes below 1 to many)

Report Schema

 

 

Report Schema 

ReportLineID

IS0100

IS0200

IS0500

IS0800

 

BS1

BS2

BS3

 

Basically want a switch statement to tell me when I have BS3 selected sum all the G/L entry table amounts that are mapped to IS1 IS2 and IS3

 

Below Measures

 

BS Switch = SWITCH(TRUE() ,
SELECTEDVALUE('Report Structure'[ReportLine ID]) = "BS3" , [BS PL Amount],
[BS Amount]
)
 
BS PL Amount = VAR SelectedDate = Max('Calendar Table'[Date])
RETURN
SUMX(
    Filter(
        ALL('Calendar Table'),
        'Calendar Table'[Date] <= SelectedDate
    ),
    Calculate(Sum(g_L_Entrys[amount]),
    FILTER(
            'g_L_Account_Categorys',
            'g_L_Account_Categorys'[reportid] IN {"IS0500" , "IS0100" , "IS0200", "IS0800" }
    )
))
 
It will not bring any figures against this, I tested the switch function with just a value and this works. 
 
TIA
1 REPLY 1
JorgePBI
Advocate I
Advocate I

First, I'll address the SWITCH statement in your "BS Switch" measure:

BS Switch = SWITCH(TRUE(),
    SELECTEDVALUE('Report Structure'[ReportLine ID]) = "BS3", [BS PL Amount],
    [BS Amount]
)

The SWITCH function here checks if the selected ReportLine ID is "BS3" and returns [BS PL Amount] if true, otherwise [BS Amount]. This part of the code looks correct at first glance.

Now, let's focus on your "BS PL Amount" measure:

BS PL Amount = 
VAR SelectedDate = MAX('Calendar Table'[Date])
RETURN
SUMX(
    FILTER(
        ALL('Calendar Table'),
        'Calendar Table'[Date] <= SelectedDate
    ),
    CALCULATE(
        SUM(g_L_Entrys[amount]),
        FILTER(
            'g_L_Account_Categorys',
            'g_L_Account_Categorys'[reportid] IN {"IS0500", "IS0100", "IS0200", "IS0800" }
        )
    )
)

This measure seems to be designed to calculate the sum of G/L Entry amounts based on specific conditions related to dates and report IDs.

Here are a few things to check:

1. Make sure that 'Report Structure'[ReportLine ID] is correctly associated with the "BS3" value in your dataset.
2. Ensure that your 'Calendar Table' and 'g_L_Account_Categorys' tables are appropriately linked with the relevant columns (e.g., Date and reportid).
3. Double-check your data to see if there are entries in the 'g_L_Entrys' table that match the conditions specified in your FILTER clauses.
If all these aspects are correct, the problem might be related to the data or how the tables are structured. Check if your data is correctly loaded, and there are no filters or slicers affecting the results when viewing your visualizations.

Additionally, consider using the DAX function SUMMARIZE to understand the data within your tables and ensure that the relationships are set up correctly.

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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