Can you use a variable to represent a slicer selection in PowerBi?

I am having a problem displaying a sum value in a gauge visual. My table has many rows with a value in each along with a date. I wrote a measure that calculates the sum of the values filtered by the type of product and whether it was sold during the day or night. The problem is there are many rows of values with the same date so when I put a date drop down slicer on the page, the measure doesn't return a value unless I make the date slicer a "range" which I don't want. I am assuming that the measure doesn't know which value to return when the slicer is in the drop down mode. How can I make this work? Can I use two variables in the DAX code for the measure that provide a date "range" based on the selection in the date drop down that the user chooses?? See below for my current code.

Table:

    DATE   |   Product Sold   |   Day or Night
--------------------------------------------------
4/1/2022            1000               N
4/1/2022             500               D
4/1/2022             800               N
4/1/2022            2000               D
4/2/2022             900               N

Measure = CALCULATE(
    SUM('Table'[Product Sold]),
    'Table'[Day or Night] IN { "N" })

So the gauge visual won't display a value based on this measure when using a drop down date slicer. It should display 1,800 when the drop down date slicer has a date of 4/1/2022

The gauge visual WILL display the sum value when using a date range slicer but I can't use that type.

I hope this makes sense. There has to be a way around it to make it work. I'm at a loss.

1 answer

  • answered 2022-05-07 09:48 sherrrry

    don't know why your gauge visual does not work. I use the same data table and the measure you wrote and it works for me. My suggestion is to update your Power BI Desktop to the latest version and try again? enter image description here enter image description here enter image description here

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum