Forum Discussion

vibhoryadav23's avatar
vibhoryadav23
Helper II
1 year ago
Solved

Data modelling suggestions

I have a dataset in which the base of my calculations dynamically changes based on various filters. Some of these filters can directly be applied but some of these are coming from other tables which ...
General Comment
Need Help
Tips and Tricks
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi vibhoryadav23 - you can write measures to dynamically calculate the percentage of each error while considering the applied filters.

    create total count of Errors

    Total Errors =
    CALCULATE(
    COUNT('Table'[Error]),
    ALL('Table'[Error]) -- Ensures all errors are considered, even if some are filtered
    )

     

    create percentage of Each Error

    Error % =
    VAR CurrentErrorCount =
    COUNT('Table'[Error])
    RETURN
    DIVIDE(CurrentErrorCount, [Total Errors], 0) * 100

     

    Create a measure that applies the selected scenario dynamically

    Scenario Filter =
    SWITCH(
    SELECTEDVALUE('Scenario Table'[Scenario]),
    "A", 1, -- No filter applied
    "B", IF('Table'[TNr] = 1, 1, 0),
    "C", IF('Table'[TNr] = 2 && 'Table'[RT] = 1, 1, 0),
    "D", IF('Table'[TNr] >= 2 && 'Table'[RT] = 1, 1, 0),
    1 -- Default to no filter
    )

     

    To exclude "OK" values from the chart while keeping them in calculations

    Error % for Chart =
    IF(
    MAX('Table'[Error]) = "OK",
    BLANK(), -- Exclude "OK" from the chart
    [Error %] -- Show percentage for other errors
    )

     

    Use a bar chart to display the percentages for each error.

    Set the Error field on the X-axis.
    Use the Error % for Chart measure as the Y-axis value.
    Add a slicer for the Scenario Table so users can select the desired scenario.

    Add any other slicers (e.g., for Date, ID, etc.) to allow users to filter the data.

     

    Hope this works. please try

rajendraongole1's avatar
rajendraongole1
Super User
1 year ago

Hi vibhoryadav23 - you can write measures to dynamically calculate the percentage of each error while considering the applied filters.

create total count of Errors

Total Errors =
CALCULATE(
COUNT('Table'[Error]),
ALL('Table'[Error]) -- Ensures all errors are considered, even if some are filtered
)

 

create percentage of Each Error

Error % =
VAR CurrentErrorCount =
COUNT('Table'[Error])
RETURN
DIVIDE(CurrentErrorCount, [Total Errors], 0) * 100

 

Create a measure that applies the selected scenario dynamically

Scenario Filter =
SWITCH(
SELECTEDVALUE('Scenario Table'[Scenario]),
"A", 1, -- No filter applied
"B", IF('Table'[TNr] = 1, 1, 0),
"C", IF('Table'[TNr] = 2 && 'Table'[RT] = 1, 1, 0),
"D", IF('Table'[TNr] >= 2 && 'Table'[RT] = 1, 1, 0),
1 -- Default to no filter
)

 

To exclude "OK" values from the chart while keeping them in calculations

Error % for Chart =
IF(
MAX('Table'[Error]) = "OK",
BLANK(), -- Exclude "OK" from the chart
[Error %] -- Show percentage for other errors
)

 

Use a bar chart to display the percentages for each error.

Set the Error field on the X-axis.
Use the Error % for Chart measure as the Y-axis value.
Add a slicer for the Scenario Table so users can select the desired scenario.

Add any other slicers (e.g., for Date, ID, etc.) to allow users to filter the data.

 

Hope this works. please try

vibhoryadav23's avatar
vibhoryadav23
Helper II
1 year ago

Hi rajendraongole1 , This is amazing!

 

Everything works well except this Measure for some reason:

 

PS: Ignore the space in "T nr". This is how it was in my data.

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    can you please check the below corrected measure:

    Scenario Filter =
    SWITCH(
    SELECTEDVALUE('Scenario Table'[Scenario]),
    "A", 1, -- No filter applied
    "B", IF(MAX('Table'[TNr]) = 1, 1, 0),
    "C", IF(MAX('Table'[TNr]) = 2 && MAX('Table'[RT]) = 1, 1, 0),
    "D", IF(MAX('Table'[TNr]) >= 2 && MAX('Table'[RT]) = 1, 1, 0),
    1 -- Default to no filter
    )

    • vibhoryadav23's avatar
      vibhoryadav23
      Helper II
      1 year ago

      It still doesnt work.

       

       

      Just to rephrase the requirement: When an option is selected from slicer, the dataset should filter on the respective conditions, with other filters still being applicable. In the dataset above, if I select scenario "B" from filter, then it should filter the dataset on TNr = 1:

       

      But this has to be done in a way that other filters are still applicable. For example, user can further filter data on 'Date' or other columns in the data for all visuals in the dashboards

       



       

       

       

      • rajendraongole1's avatar
        rajendraongole1
        Super User
        1 year ago

        Hi vibhoryadav23 - I hope this works as per your description

         

        Then create a dynamically applies the condition based on the selected scenario. Here’s an updated version of the measure

        Scenario Filter =
        SWITCH(
        SELECTEDVALUE('Scenario Table'[Scenario]),
        "A", 1, -- No filter
        "B", IF(SELECTEDVALUE('Table'[TNr]) = 1, 1, 0),
        "C", IF(SELECTEDVALUE('Table'[TNr]) = 2 && SELECTEDVALUE('Table'[RT]) = 1, 1, 0),
        "D", IF(SELECTEDVALUE('Table'[TNr]) >= 2 && SELECTEDVALUE('Table'[RT]) = 1, 1, 0),
        1 -- Default: No filter
        )