Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Date Dimension Filter

Hi All,   Is there any alternate way to achieve this below blog? https://community.powerbi.com/t5/Desktop/Column-Selection-using-a-Disconnected-table/m-p/820490#M394243 I don't want to duplicate ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    How about create a calculated table and create a measure as a visual level filter?

    Table 2 = 
UNION (
    SELECTCOLUMNS (
        'Table',
        "date", 'Table'[date].[Month] & 'Table'[date].[Year],
        "value", 'Table'[value],
        "period", "Month"
    ),
    SELECTCOLUMNS (
        'Table',
        "date", 'Table'[date].[Year] & 'Table'[date].[Quarter],
        "value", 'Table'[value],
        "period", "Quarter"
    ),
    SELECTCOLUMNS (
        'Table',
        "date", 'Table'[date].[Year],
        "value", 'Table'[value],
        "period", "Year"
    )
)

Measure = IF(SELECTEDVALUE('Table 2'[period])=SELECTEDVALUE(period[period]),1,BLANK())

    Result would be shown as below.

     

    Pbix as attached.

     

    Best Regards,

    Jay

    Community Support Team _ Jay Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

 

How about create a calculated table and create a measure as a visual level filter?

Table 2 = 
UNION (
    SELECTCOLUMNS (
        'Table',
        "date", 'Table'[date].[Month] & 'Table'[date].[Year],
        "value", 'Table'[value],
        "period", "Month"
    ),
    SELECTCOLUMNS (
        'Table',
        "date", 'Table'[date].[Year] & 'Table'[date].[Quarter],
        "value", 'Table'[value],
        "period", "Quarter"
    ),
    SELECTCOLUMNS (
        'Table',
        "date", 'Table'[date].[Year],
        "value", 'Table'[value],
        "period", "Year"
    )
)

Measure = IF(SELECTEDVALUE('Table 2'[period])=SELECTEDVALUE(period[period]),1,BLANK())

Result would be shown as below.

 

Pbix as attached.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    I think it is working fine.

     

    But i have few quetions

    1. What is the purpose of giving the Measure in Visual Level filter? I coundn't able to understand the reason behind it.

    2. Do we need to associate this Calculated table and (Main)Table in the data model? or just let it be two island table in power bi data model.

    3. Also i have doubt in the below measure whether it is period[period] or 'Table 2'[period]? I think it is Table 2'[period].

    Measure = IF(SELECTEDVALUE('Table 2'[period])=SELECTEDVALUE(period[period]),1,BLANK())

     

    Thanks in Advance

     

    Note:Unfortunately i couldn't able to open the PBIX file because of issues from my side. That is the reason i am asking these queries.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous ,

       

      1. The purpose of the Measure here is to determine whether the period in the table is equal to the selected value from slicer and the purpose of giving the Measure in Visual Level filter is to filter blank value.

      2. There's no need to associate Calculated table and (Main)Table.

      3. Actually, the Measure is fine and it was a little mistake of mine that I didn't mention that i have created another slicer table.

       

      Best Regards,

      Jay

      Community Support Team _ Jay Wang

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous ,

        Cant we create Weekday same as  Month,Year and quarter in the below formula?

        Table 2 = 
UNION (
    SELECTCOLUMNS (
        'Table',
        "date", 'Table'[date].[Month] & 'Table'[date].[Year],
        "value", 'Table'[value],
        "period", "Month"
    ),
    SELECTCOLUMNS (
        'Table',
        "date", 'Table'[date].[Year] & 'Table'[date].[Quarter],
        "value", 'Table'[value],
        "period", "Quarter"
    ),
    SELECTCOLUMNS (
        'Table',
        "date", 'Table'[date].[Year],
        "value", 'Table'[value],
        "period", "Year"
    )
)

Measure = IF(SELECTEDVALUE('Table 2'[period])=SELECTEDVALUE(period[period]),1,BLANK())

        Thanks