Help needed with using multiple slicers for parameters and filters in visualisations

[scott_m]

Hi all. In Power BI I’m trying to replicate a report that was created by a former colleague in Tableau, however I’m encountering issues with using multiple interacting parameters and other slicers in individual visualisations.

I’m not sure whether my issue is due to incompatible visualisation types, incorrect measures, incorrect relationships, something else, or some combination of these.

Here are links to some files to help demonstrate what I need:

• Existing Tableau report screenshot 1
• Existing Tableau report screenshot 2
• Power BI report draft
• Excel spreadsheet of main table (‘Claims’)
• CSV of ‘DATE’ table

There are seven slicers in the report:

Metric

Claim Range

Date to Use

Date Range (for bar charts)

Colour Breakdown

Breakdown

Date Range (for trend line chart)

There are five visualisations in the report but, for the sake of brevity, I’ll just focus on the most interactive stacked bar chart of the report (which visualises “Metric” by “Breakdown” by “Colour Breakdown” for “Claim Range”). In it I need to display the following:

The measure/metric to be represented as the values of the bars themselves can be chosen by the "Metric" parameter of either:

- The count of ‘Claims’[Claim Number]

- The sum of ‘Claims’[Total Payments]

Y-axis: the "Breakdown" parameter is able to switch the axis dimensions between the following columns:

- ‘Claims’[Injury Nature]

- ‘Claims’ [Injury Location]

- ‘Claims’ [Mechanism of Injury]

- ‘Claims’ [Agent of Injury]

- ‘Claims’ [Breakdown Agent]

No matter which "Breakdown" dimension is chosen as the y-axis, the bars should be able to then be able to be dissected by the "Colour Breakdown" parameter which can switch between the following columns (i.e. so that the bar is split into sub-sections that represent the measure of each value within the following columns):

- ‘Claims’[Count]

- ‘Claims’[Claim Liability]

- ‘Claims’[Claim Status]

- ‘Claims’[Division]

- ‘Claims’[Branch]

- ‘Claims’[Physical/Psychological]

The chart can also be filtered based on the "Claim Range" which looks at the ‘Claims’[Claim Status] column for the following:

- Active Cases: that is, where the Claim Status is equal to either "Open" or "Re-opened"

- Claims Lodged in Date Range: that is, where the Claim Status is equal to any value

The chart can then be further filtered based on a "Date Range" filter, based on date ranges in the calculated ‘Date Range’ table. The dates in the ‘Date Range’ table have an active relationship to a column containing unique dates in the separate ‘DATE’ table.

The "Date Range" filter itself reads off a separate "Date to Use" parameter which can be switched between either of the following two date columns in the "Claims" table:

- Accident Date

- Registration Date

Hopefully having someone help me to understand how to build this particular visualisation will allow me to then figure out the other four visualisations in the report.

I’m happy to clarify anything as I know that my understanding and use of terminology are slightly lacking, sorry. Any help would be greatly appreciated!

[MFelix]

Hi @scott_m ,

I was looking into the report you shared and the interactions seem to be working, is your problem on the Registration / Accident date?

That is the only question I see because you are using the simple SUM of the values and when you want to change the calculations you need to make use of USERELATIONSHIP you also need to convert you accident and  register date to date format and not text:

The syntax should be similar to:

Sum of Count = IF( SELECTEDVALUE('Date to Use'[Date to Use]) = "Accident Date", CALCULATE( SUM('Claims'[Count]),USERELATIONSHIP(Claims[Accident Date], 'DATE'[FULL_DATE] )),CALCULATE( SUM('Claims'[Count]),USERELATIONSHIP(Claims[Registration Date], 'DATE'[FULL_DATE] )))

check the PBIX file and Page 1 and check if the interactions match what you need, If they do not match please let me know what is the final result you need.

[scott_m]

Hi @MFelix,

This seems to get me a bit closer to what I need, thanks. However, the issue is that the Year, Quarter, Month, Day slicer is affecting the chart when the Claim Status slicer has values of Open and/or Re-Opened selected. When those values are selected, the chart should not be affected by any date filter. I need to have a Claim Range slicer that filters rows based on either:

• Active Claims (i.e. claims that have a value in the Claim Status column of either Open or Re-opened; when this slicer value is selected only the [Metric], [Colour Breakdown] and [Breakdown] slicers affect the visualisation)
• Claims Lodged in Date Range (i.e. claims that have any value in the Claim Status column, but the date range is affected by the [Date to Use] and [Date Range] slicers)

Rather than the Year, Quarter, Month, Day slicer I need to use the values in my initial Date Range calculated table in a slicer.

Also, when I try to manually add the two parameters (Colour Breakdown and Metric) as the legend, I'm only able to add one - they just replace each other every time I try to drag and drop another parameter into the Legend field. I noticed that you had actually answered a question in the past regarding this here. How did you manage to add them both in this report? Was it via first adding all the fields into a table visualisation then changing the visualisation to a bar chart visualisation?

[MFelix · Accepted solution]

Hi @scott_m ,

When you select Open/ReOpen from the status, you refer that the chart should not be affected by any date filter, what does this mean exactly, you get all the data from the table?

For this you need to update your measures to something similar to this:

Sum of Count = SWITCH(
			TRUE(),
			SELECTEDVALUE(Claims[Claim Status]) IN {
				"Open",
				"Re-Opened"
			}, SUM('Claims'[Count]),

			IF(
				SELECTEDVALUE('Date to Use'[Date to Use]) = "Accident Date",
				CALCULATE(
					SUM('Claims'[Count]),
					USERELATIONSHIP(
						Claims[Accident Date],
						'DATE'[FULL_DATE]
					), 
                    'DATE'[FULL_DATE] in values('Date Range'[FULL_DATE])
				),
				CALCULATE(
					SUM('Claims'[Count]),
					USERELATIONSHIP(
						Claims[Registration Date],
						'DATE'[FULL_DATE]
					), 
                    'DATE'[FULL_DATE] in values('Date Range'[FULL_DATE])
				)
			)
		)

This measure also solves the question about the date range.

Concerning the split of the measure by Colour BreakDown and Metric, not really sure what you mean?
You want to present both measures claims and total payments in the same bar chart with the split of the Color breakdown? Can you give more specifications on this?

I apologize for the questions but is a complex request so trying to do it a question at a time.

[scott_m]

Thanks @MFelix - this definitely put me on the right path. The two SWITCH statements I ended up using were:

Metric - Claims =

SWITCH (

    TRUE (),

    SELECTEDVALUE ( 'Filter - Claim Range'[Claim Range] ) = "Active Claims",

    CALCULATE (

        COUNTROWS ('Claims'),

        'Claims'[Active Claim Flag] = TRUE

    ),

    SELECTEDVALUE ( 'Filter - Claim Range'[Claim Range] ) = "Claims Lodged in Date Range"

        && SELECTEDVALUE ( 'Filter - Date to Use'[Date to Use] ) = "Registration Date",

    CALCULATE (

        COUNTROWS ('Claims'),

        'Claims'[Registration Date] >= MIN ( 'DATE'[FULL_DATE] )

            && 'Claims'[Registration Date] <= MAX ( 'DATE'[FULL_DATE] )

    ),

    SELECTEDVALUE ( 'Filter - Claim Range'[Claim Range] ) = "Claims Lodged in Date Range"

        && SELECTEDVALUE ( 'Filter - Date to Use'[Date to Use] ) = "Accident Date",

    CALCULATE (

        COUNTROWS ('Claims'),

        'Claims'[Accident Date] >= MIN ( 'DATE'[FULL_DATE] )

            && 'Claims'[Accident Date] <= MAX ( 'DATE'[FULL_DATE] )

    ),

    BLANK ()

)

and

Metric - Total Payments =

SWITCH (

    TRUE (),

    SELECTEDVALUE ( 'Filter - Claim Range'[Claim Range] ) = "Active Claims",

    CALCULATE (

        SUM ('Claims'[Total Payments]),

        'Claims'[Active Claim Flag] = TRUE

    ),

    SELECTEDVALUE ( 'Filter - Claim Range'[Claim Range] ) = "Claims Lodged in Date Range"

        && SELECTEDVALUE ( 'Filter - Date to Use'[Date to Use] ) = "Registration Date",

    CALCULATE (

        SUM ('Claims'[Total Payments]),

        'Claims'[Registration Date] >= MIN ( 'DATE'[FULL_DATE] )

            && 'Claims'[Registration Date] <= MAX ( 'DATE'[FULL_DATE] )

    ),

    SELECTEDVALUE ( 'Filter - Claim Range'[Claim Range] ) = "Claims Lodged in Date Range"

        && SELECTEDVALUE ( 'Filter - Date to Use'[Date to Use] ) = "Accident Date",

    CALCULATE (

        SUM ('Claims'[Total Payments]),

        'Claims'[Accident Date] >= MIN ( 'DATE'[FULL_DATE] )

            && 'Claims'[Accident Date] <= MAX ( 'DATE'[FULL_DATE] )

    ),

    BLANK ()

)