DAX Table not responding to slicer values

[CamronBute]

I'm trying to create a table of filtered values that responds to a slicer selection. However, it doesn't seem to work... SELECTEDVALUE() is returning all rows in the table, even though I have a slicer selected.

PBIX link: https://1drv.ms/u/s!AhcctnI0qBnIjkh23Ov1Qm0-u1wq?e=AWvqJa

[Greg_Deckler]

@CamronBute - That's because you have no relationship between your tables. In particular, between CY & FY table and your Periods and Periods To Display tables. That's sort of required for slicers to work betwen tables unless you form the relationship within a measure.

[CamronBute]

@Greg Thank you for your response. The data I have doesn't seem to conceptually support a relationship between these tables. However, if I was to have a relationship, SELECTEDVALUE() would return the appropriate value? I was hoping that the use of a DAX table would eliminate the need to create a relationship

[Greg_Deckler]

SELECTEDVALUE is working correctly, I can see that in your Card visualization. If I select CY, I see "It's a CY". If I select FY, I see "It's a FY". So that means SELECTEDVALUE is working just fine as expected.

Perhaps we should back-up and you should explain what you are trying to achieve. 

[CamronBute]

@Greg_DecklerThank you. I've updated the PBIX file to clarify the example.

Periods is a dimension I want to use, linked to a fact table. However, the user wants to have a selector on the report that selects either CY or FY. This is set to be a forced single selection, so something is always selected. When CY is selected, they are shown a list of periods that are in the current CY. When FY is selected, they are shown a list of periods in the current FY. CY and FY overlap, so months can be in both the current CY and the current FY. They can then select periods from that list, which are in turn used to filter the fact table I have.

The issue I'm having is that even though I've selected a value in the CY/FY table, the DAX table that will show periods I can show to the user doesn't seem to be respecting that. The measure works just fine, but the DAX table doesn't. I had included the measure in my original file to show that it's working outside of the DAX table.

In the latest version of the file, CY should show 6 months under the Selected Periods, and FY should show 3. However, no matter what I select in the slicer, I only see 3 periods.

[sturlaws · Accepted solution]

Hi @CamronBute 

the reason the number of periods does not change is that the table 'Periods to display' is a calculated table. Calculated tables does not respond to slicers, in the same way as calculated columns does not respond to slicers.

You can create a measure like this:

filterMeasure =
VAR _type =
    CALCULATE ( SELECTEDVALUE ( 'CY & FY'[CY & FY] ) )
RETURN
    SWITCH (
        TRUE (),
        _type = "CY"
            && COUNTROWS ( FILTER ( Periods, [Is In Current CY] = "Yes" ) ) >= 1, 1,
        _type = "FY"
            && COUNTROWS ( FILTER ( Periods, [Is In Current FY] = "Yes" ) ) >= 1, 1
    )

and use that to filter the visual showing the available months. I have updated your pbix-file, you find it attached below.

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.