Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

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...
Need Help
  • MFelix's avatar
    MFelix
    2 years ago

    Hi Anonymous ,

    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.

MFelix's avatar
MFelix
Super User
2 years ago

Hi Anonymous ,

 

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      MFelix
      Super User
      2 years ago

      Hi Anonymous ,

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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 ()
        )